Downloads
Power Query MDateIntermediate

Date.AddYears

Adds a number of years to a date. A February 29 that falls in a non-leap year becomes February 28.

Syntax
Date.AddYears(dateOrDateTime as any, numberOfYears as number) as any

Returns: The new date, or null when the input is null.

What it does

Date.AddYears moves a date by whole years. It is used for asset lives, multi year contracts and anniversary dates, such as the end of a three year warranty. A leap day, February 29, moves to February 28 in a year that is not a leap year, which is the same clamp rule the month function uses.

Syntax and arguments

Example

An asset is bought on March 1, 2026, with a five year useful life. The end of its life, and the year the depreciation schedule ends, are calculated from the purchase date.

let
    PurchaseDate = #date(2026, 3, 1),
    EndOfLife = Date.AddYears(PurchaseDate, 5),
    LastYear = Date.Year(EndOfLife)
in
    {EndOfLife, LastYear}

The end of life is March 1, 2031, and the last year of depreciation is 2031. The schedule runs from the purchase date to that date.

Example: a second, different use

Find a contract's renewal date three years after its start, when the start is February 29 in a leap year, to see how the clamp rule applies.

Things to watch

In business terms

Asset lives, warranties and multi year leases run on anniversary dates. Setting those dates from the start date with one function keeps the depreciation and the lease schedule in line with the contract.

Related functions

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