Downloads
Power Query MDateIntermediate

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.

Syntax
Date.AddMonths(dateOrDateTime as any, numberOfMonths as number) as any

Returns: 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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics