Date.AddYears returns a date shifted forward or back by a number of years. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.
If you want to move a date a set number of years ahead, like working out a renewal or expiry date, this is the function you reach for. You give it a date and how many years to add, and it hands back the shifted date.
Syntax of Date.AddYears Function
Date.AddYears(dateTime as any, numberOfYears as number) as any
where
dateTime(required,any). Thedate,datetime, ordatetimezonevalue to shift.numberOfYears(required,number). The number of years to add. A negative number subtracts years instead.
Returns: the shifted value, of the same kind as the input (a date stays a date, a datetime stays a datetime).
In plain terms, it takes your date and the number of years, and returns that same date moved by that many years.
Example 1: Add years to a date
Add 5 years to a fixed date.
Date.AddYears(#date(2024,6,10),5)
Result: 2029-06-10
The year jumps from 2024 to 2029, while the month and day stay put.
Example 2: Subtract years with a negative number
Pass a negative number to move the date backward.
Date.AddYears(#date(2024,6,10),-3)
Result: 2021-06-10
A negative numberOfYears subtracts, so the date lands 3 years earlier.
Example 3: Leap day edge case
Add 1 year to February 29 in a leap year.
Date.AddYears(#date(2024,2,29),1)
Result: 2025-02-28
Since 2025 has no February 29, the result clamps to the last valid day of the month, February 28.
Example 4: Add a renewal date column to a table
The most common use is adding a shifted date as a new column.
Say you have a Subscriptions query with a Member and a StartDate column.
Here is the starting data:
| Member | StartDate |
|---|---|
| Riya | 2022-08-15 |
| Marcus | 2023-11-03 |
You want a RenewBy column that falls 3 years after each start date:
let
Source = Excel.CurrentWorkbook(){[Name="Subscriptions"]}[Content],
Typed = Table.TransformColumnTypes(Source,{{"StartDate",type date}}),
Result = Table.AddColumn(Typed,"RenewBy",each Date.AddYears([StartDate],3),type date)
in
Result
The result adds the shifted date to every row:
| Member | StartDate | RenewBy |
|---|---|---|
| Riya | 2022-08-15 | 2025-08-15 |
| Marcus | 2023-11-03 | 2026-11-03 |
Each RenewBy value is its StartDate moved forward 3 years.
Things to keep in mind with Date.AddYears
- February 29 clamps to February 28 in a non-leap year. Shifting a leap day into a year that has no February 29 lands on February 28 (Example 3), not March 1.
- It shifts only the year. The month and day stay the same (apart from the leap-day clamp), unlike adding days, which drifts across leap years.
- The input must be a date or datetime value, not text. A text date like
"2024-06-10"throws anExpression.Error. Convert it to a date first withDate.FromText("2024-06-10"). - A
datetimeinput keeps its time component. Only the year part moves, so the hours, minutes, and seconds carry through unchanged.
Performance and query folding
Date.AddYears is cheap date arithmetic that runs fast even over large columns. Against a foldable source like SQL Server, it can fold into the source query, so the shift happens at the database rather than locally. On Excel, CSV, or folder sources it runs locally, which is fine for typical row counts.
Common questions about Date.AddYears
Why use Date.AddYears instead of adding 365 days?
Adding 365 days ignores leap years, so over several years your date drifts off by a day or more. Date.AddYears moves the calendar year directly and keeps the same month and day, so a renewal always lands on the right date.
How do I calculate someone’s age with it?
Age is a date difference, not a year shift, so you compare years instead. Take today’s date from DateTime.LocalNow, then use Date.Year(Date.From(DateTime.LocalNow())) - Date.Year([BirthDate]) for a rough age and adjust by whether this year’s birthday has passed.
List of All Power Query Functions
Related Power Query Functions / Articles:
