# How to check if a year is a leap year in Excel

Ask DATE for February 29: `=IF(MONTH(DATE(A2,2,29))=2,"Leap year","Common year")`. In a common year it rolls over to March.

**Excel:** `=IF(MONTH(DATE(A2,2,29))=2,"Leap year","Common year")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Year | Type |
| 2024 |  |
| 2025 |  |
| 2026 |  |
| 2028 |  |
| 2100 |  |
| 2000 |  |

Formula in B2, filled to B7: `=IF(MONTH(DATE(A2,2,29))=2,"Leap year","Common year")`

Result: Leap year; Common year; Common year; Leap year; Common year; Leap year

## How it works

DATE accepts February 29 for any year, but in a common year it rolls over to March 1. If the month is still 2, the date exists and the year is a leap year. The calendar rules come for free: 2100 is not a leap year, 2000 is.

## Other ways

- `=IF(DAY(DATE(A2,3,0))=29,"Leap year","Common year")`: Day 0 of March is the last day of February.
- `=IF(OR(MOD(A2,400)=0,AND(MOD(A2,4)=0,MOD(A2,100)<>0)),"Leap year","Common year")`: The rule written out: every 4 years, except century years not divisible by 400.

## Common mistakes

- `MOD(A2,4)=0` alone calls 2100 a leap year. Century years are leap years only when divisible by 400.
- Excel treats 1900 as a leap year for compatibility with Lotus 1-2-3, so the DATE test is wrong for 1900. Use the MOD rule for years before 1901.

## Questions

**How do I check the year of a date instead of a year number?**

Wrap it in YEAR: `=MONTH(DATE(YEAR(A2),2,29))=2`.

**How many days are in the year?**

`=IF(MONTH(DATE(A2,2,29))=2,366,365)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/check-if-a-year-is-a-leap-year