Add or Subtract Days, Months, or Years From a Date

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 .