Date.AddMonths
Adds a number of months to a date. When the day does not exist in the new month, the result is the last day of that month.
Date.AddMonths(dateOrDateTime as any, numberOfMonths as number) as anyReturns: The new date, or null when the input is null.
What it does
Date.AddMonths moves a date forward or back by months. It is the function behind monthly schedules, lease and loan payment dates, and the fiscal year offset. Its key rule is the month end: January 31 plus one month is February 28 (or 29 in a leap year), because February has no 31st. The result is always a real date.
Syntax and arguments
- dateOrDateTime: the starting date or datetime.
- numberOfMonths: the number of months to add. A negative number goes back.
Example
A lease starts on January 31, 2026, and the rent is due on the same day of each month. The team lists the first few due dates, and the ones that fall in short months are checked.
let
LeaseStart = #date(2026, 1, 31),
DueDates = List.Transform({0..3}, each Date.AddMonths(LeaseStart, _)),
AsTable = Table.FromList(DueDates, Splitter.SplitByNothing(), {"DueDate"}, null, ExtraValues.Error)
in
AsTable
The list is January 31, February 28, March 31 and April 30, 2026. February and April have no 31st, so their due dates fall on the last day of the month. If the lease needs the 31st in every month, the team must agree the rule with the landlord, since the function will not skip February.
Example: a second, different use
Find the start of a fiscal year that begins on July 1 by moving a date six months forward, taking the calendar year, and moving back. The day clamp does not affect that use, because only the year and month are taken.
Things to watch
- A month end date does not stay at the 31st. August 31 plus six months is February 28 in a non-leap year.
- Subtracting months has the same clamp rule.
- A fiscal month or year is found by adding a fixed number of months and reading the calendar year or month.
- Null in gives null out.
In business terms
Loan and lease schedules, subscription billing, depreciation and fiscal calendars all depend on month arithmetic. Knowing how the function handles month ends is how a schedule avoids a short month surprise in the cash forecast.