Try:
(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.
See also: DAY(), EOMONTH()
LinkedIn
Twitter
