How do I add months to Date

[Fusion_Incense]

For example I have start date: 2017/10/28 and want to add 60 months from that date, the result should be 2022/09/28 of which 2017/10/28 count as the first 1 month

[Mike_A]

I find date math in Appsheet can get confusing. Maybe this help document can assist?

help.appsheet.com

Date and Time Expressions

Incorporate dates and times into the behavior of your app.

From that document:

• FLOOR(((HOUR([EndDate] - [StartDate])) - ([Years] * 365.25 * 24.0)) / (30.4 * 24.0)) : the number of months between the start and end dates. It assumes virtual column Years contains the number of years computed using the formula above.

And a thread from @Fabian talking about a similar problem: Adding Months Would be nice to simply add mo...

[Steve · Accepted solution]

Try:

(EOMONTH([StartDate], ([MonthsAhead] - 2)) + DAY([StartDate]))

1. [StartDate] is the starting date (a Date or DateTime value) from which you want to compute a new date. Because the start date is used twice in this expression, it’s best that the date be either a fixed literal value or a value contained in a column, to avoid inefficiently recomputing the value twice.

2. [MonthsAhead] is the number of months (a Number value) beyond the starting date you want the new date.

3. EOMONTH([StartDate], ([MonthsAhead] - 2)) computes the date of the last day of the month ([MonthsAhead] - 2) months after the month described by [StartDate]. EOMONTH() interprets a value of 0 as meaning the month of the starting date, but you want a value of 1 to mean the starting month, so we subtract 1 to translate your value to what EOMONTH() wants. For our computation below (see (4)), we want the end date of the month prior to the desired target date, so we have to subtract another 1.

4. ... + DAY([StartDate]) adds the month day of the starting month to the computed end-of-month date (...; from (3)). Because the computed end-of-month date is for the month prior to the desired target date, adding the month day pushes the computed date into the desired month and gives the final computed date the same day of the month as the start date.

EOMONTH() has the advantage of dealing with the different month lengths and leap years automatically.

See also: DAY(), EOMONTH()

[Mike_A]

@Steve - great and clear explanation again. When you do these write-ups, i really appreciate the time you spend with explanation. I can usually figure this out but it’s a lot of trial and error.

Any chance the write-ups can be added to Tips and Tricks, as well as to the help documentation? Hate to lose all your good work!

[Steve]

The EOMONTH() help page already has examples that could reasonably lead someone to solving this problem.

[Mike_A]

I checked Date and Time Expressions Help and missed EOMONTH(). My fault

[Steve]

Updated. Thanks!

[Fusion_Incense]

@Steve Thank You very much, It worked, now I’ll try to absorbed the logic.

[Steve]

EOMONTH(TODAY(), 0) gives the date of the last day of this month (0).

EOMONTH(TODAY(), -1) gives the date of the last day of last month (-1).

EOMONTH(TODAY(), -1) + 1 gives the date of the first day of this month (i.e., one day after (+ 1) the last day of last month (-1)).

EOMONTH(TODAY(), -1) + DAY(TODAY())) gives the date of today.

The EOMONTH() help page has more examples.

[richoaaa]

I don't think this works when you use a column value for the month offset-1. EOMonth only seems to accept one value as the offset number and can't deal with any calculated variant. For context, I want to schedule months in advance, if I use use the months in advance + days it will result in the date being one month too late!

Error:The added or subtracted value results in an un-representable DateTime. Parameter name: months/n

[Steve]

> EOMonth only seems to accept one value as the offset number and can't deal with any calculated variant.

Works for me. 😕

> I want to schedule months in advance, if I use use the months in advance + days it will result in the date being one month too late!

Correct. Go back and re-read the help doc.