Problem

How to add or subtract X amount of days/months/years from a date.

Solution

Use either of the follow methods:

  • Create a result column as follows (note:  MMDDYY is the date wrapper; choose the one that fits the date format on your AS/400)
    DATE(DateField, MMDDYY) + XX days (for addition)

    or

    DATE(DateField, MMDDYY) - XX days (for subtraction)

    Replace "days" with "months" or "years" as needed.

  • Create a result column as follows (note:  XX is the number of days.  You do not type the word days at the end of the equation, as you do in Example #1). This is only for subtracting days, rather than months or years.
    DAYS(Date(DateField, MMDDYY)) - XX

    or

    DAYS(Date(DateField, MMDDYY)) +  XX

 

To do the calculation and then convert the answer back to a date, the equation would be as follows:

DATE(DAYS(Date(DateField, MMDDYY)) - XX)  

Note: No date wrapper is used to convert the DAYS to a DATE

For calculations involving CURDTE(), the equation would be similar to the following:

DATE(DAYS(CURDTE()) - XX) 

Note: CURDTE() and ISO dates do not need a date wrapper

 

Ref#: 1475513

 

 

 

Meet your business goals with help from the experts. HelpSystems offers Professional Services to assist you with training, consulting, product services and managed services .


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: November 16, 2018