Date.IsLeapYear Function in Power Query M (5 Easy Examples)

Date.IsLeapYear checks whether the year of a given date is a leap year and returns true or false. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to test whether a year has 366 days, or flag the leap years across a column of dates, this is the function you reach for.

Table of Contents

Syntax of Date.IsLeapYear Function

Date.IsLeapYear(dateTime as any) as nullable logical

where

dateTime (required, any). A date, datetime, or datetimezone value. The function reads the year portion of this value and reports whether that year is a leap year. There is no year-number overload, so you have to pass a temporal value, not a plain integer.

Returns: a logical value, either true or false. If dateTime is null, it returns null.

In plain terms, you give it a date and it tells you whether that date’s year is a leap year.

Example 1: Check a leap year

Test whether 2024 is a leap year by passing any date in that year.

Date.IsLeapYear(#date(2024,1,1))

Result: true

2024 is divisible by 4, so the function returns true.

Example 2: Check a non-leap year

Date.IsLeapYear(#date(2023,1,1))

Result: false

2023 is not divisible by 4, so it returns false.

Example 3: A century year that is NOT a leap year (1900)

Century years are the classic edge case. The rule is that a year divisible by 100 is only a leap year if it is also divisible by 400.

Date.IsLeapYear(#date(1900,1,1))

Result: false

1900 is divisible by 100 but not by 400, so it is not a leap year.

Example 4: A century year that IS a leap year (2000)

Date.IsLeapYear(#date(2000,1,1))

Result: true

2000 is divisible by 400, so it is a leap year. This is the pair people get wrong most often: 1900 is false, 2000 is true.

Example 5: Flag the leap years across a table

When you have a column of years, you can flag each one in a single step.

Say you have a ProjectDeadlines query with a Project and a Year column.

Here is the starting data:

Project	Year
Apollo	2020
Borealis	2021
Cascade	2028
Drift	1900

The Year column holds plain numbers, so wrap each one in #date([Year],1,1) to build a date before passing it to Date.IsLeapYear:

let
    Source = Excel.CurrentWorkbook(){[Name="ProjectDeadlines"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Year",Int64.Type}}),
    AddFlag = Table.AddColumn(Typed,"IsLeapYear",each Date.IsLeapYear(#date([Year],1,1)),type logical)
in
    AddFlag

This adds an IsLeapYear column that is true or false for every row.

The result:

Project	Year	IsLeapYear
Apollo	2020	true
Borealis	2021	false
Cascade	2028	true
Drift	1900	false

2020 and 2028 are leap years, 2021 is not, and the century year 1900 comes back false.

If you only want the leap years, drop this flag into Table.SelectRows instead of adding a column.

Things to keep in mind with Date.IsLeapYear

It takes a date, not a year number. Date.IsLeapYear(2024) errors because the argument must be a temporal value. To test a bare year, wrap it with #date(2024,1,1). The day and month you pick do not matter, only the year is read.
Century years follow the divisible-by-400 rule. A year divisible by 100 is a leap year only if it is also divisible by 400. So #date(1900,1,1) is false but #date(2000,1,1) is true.
null in returns null out. Because the return type is nullable logical, passing null gives back null, not an error and not false. Guard a blank-prone date column before relying on the result.
It pairs with Date.DaysInMonth for February. Use Date.IsLeapYear when you only need the true/false flag, and Date.DaysInMonth(#date(y,2,1)) when you need the actual count of 29 vs 28 days.
For arithmetic on dates, reach for a different function. Date.IsLeapYear only reports the leap-year flag. To move a date forward or back use Date.AddDays, and to measure the gap between two dates see calculate date difference in Power Query.

Common questions about Date.IsLeapYear

How do I check whether a year is a leap year when I only have the year number?

Wrap the year in #date(year,1,1) and pass that. There is no integer-year version of the function, so Date.IsLeapYear(2024) will not work, but Date.IsLeapYear(#date(2024,1,1)) will.

Why does February have 29 days in some years?

Because those years are leap years. Date.IsLeapYear gives you the flag, and Date.DaysInMonth(#date(y,2,1)) returns 29 in a leap year and 28 otherwise.

List of All Power Query Functions

Related Power Query Functions / Articles: