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
I find date math in Appsheet can get confusing. Maybe this help document can assist?
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.
(EOMONTH([StartDate], ([MonthsAhead] - 2)) + DAY([StartDate]))
[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.
[MonthsAhead]is the number of months (a Number value) beyond the starting date you want the new date.
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.
... + 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.
@Steve Thank You very much, It worked, now I’ll try to absorbed the logic.
@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!
EOMONTH(TODAY(), 0) gives the date of the last day of this month (
EOMONTH(TODAY(), -1) gives the date of the last day of last month (
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 (
EOMONTH(TODAY(), -1) + DAY(TODAY())) gives the date of today.
The EOMONTH() help page has more examples.
The EOMONTH() help page already has examples that could reasonably lead someone to solving this problem.