Date.IsLeapYear
Tests whether the year of a date is a leap year, which has 366 days.
Date.IsLeapYear(dateTime as any) as logicalReturns: true for a leap year, false otherwise, or null when the input is null.
What it does
Date.IsLeapYear tells you whether a year has 366 days, with February 29. It is used when a calculation depends on the length of the year, such as a daily interest rate based on the actual days in the year, or a year to date percentage. Most of the time the answer does not change the result, but in some calculations it changes the cent.
Syntax and arguments
- dateTime: a date or a datetime value. The year of that date is tested.
Example
A loan charges interest on the actual number of days in the year. The team checks whether the year is a leap year to pick the right day count.
let
Years = #table(type table [Year = number], {{2026}, {2028}}),
Checked = Table.AddColumn(Years, "IsLeap", each Date.IsLeapYear(#date([Year], 1, 1)), type logical),
DaysInYear = Table.AddColumn(Checked, "Days", each if [IsLeap] then 366 else 365, Int64.Type)
in
DaysInYear
2026 is not a leap year, so it has 365 days. 2028 is a leap year, so it has 366. The loan interest for each year uses the matching day count.
Example: a second, different use
Check that a year end date test does not assume February has 28 days in every year.
Things to watch
- The test uses the year of the date given, so pass a date from the year you care about.
- A year divisible by 100 is not a leap year unless it is divisible by 400. The function applies that rule, so you do not need to.
- The test is for the length of the year. For the days in February alone, Date.DaysInMonth already handles the leap day.
- Null in gives null out.
In business terms
Interest accrual, daily rate calculations and year to date ratios depend on the true length of the year. Checking for a leap year in the query keeps those calculations exact in the years when it matters.