## Determine leap year with #Excel Today is a special day.  Today is February 29th, which happens only once every four years.  And today is Saturday, when I usually post a new blog.  So let’s talk about how to determine if a year is leap year using Excel.

### First of all, what is leap year?

We may find a detailed explanation in WikipediaBelow is part of the article describing how to determine a leap year without needing an Excel formula.

…in the Gregorian calendar, each leap year has 366 days instead of 365, by extending February to 29 days rather than the common 28. These extra days occur in each year which is an integer multiple of 4 (except for years evenly divisible by 100, which are not leap years unless evenly divisible by 400)

https://en.wikipedia.org/wiki/Leap_year

But you know, this is an Excel blog and I am an Excel nerd.  That’s why I am going to use Excel to solve this problem.  A simple formula indeed.

`=DAY(EOMONTH(DATE(A2,2,1),0))=29'where A2 is the year input; works for year 1901 onwardNote: It's an known issue that Excel mistreats the year 1900 as leap year. 😐`

How the formula works?

1. The most inner portion DATE(A2,2,1) returns the first date of February of the year;
2. Wrapping it with EOMONTH, Excel returns the last date of February of the year;
3. The outermost DAY() function returns the day portion of the date;
4. As a result, if it is 29, it’s a leap year.

As simple as that.  🙂

Indeed, we may use a much shorter formula to achieve the same.

### =DAY(DATE(A2,2,29))=29

When we hard-code the day portion as 29, and when there is no 29th in that February, Excel is smart enough to return March 1st instead.  Therefore the outside DAY() function would return 1, which is not equal to 29.

Make sense!?

### Want some challenge?

Well, you may think it is too easy to determine if a year is leap year of not.  Really no need to use Excel to do that.  How about if I twist the question a bit…

When is the next February 29th which is a Saturday?

Think a bit before you continue to read this…

Tip: We can do it in a single formula by using Dynamic Array

The answer is February 29th, 2048.

`=MIN(IF(WEEKDAY(EOMONTH(DATE(SEQUENCE(10,1,2024,4),2,1),0),2)=6,EOMONTH(DATE(SEQUENCE(10,1,2024,4),2,1),0)))Note: This formula is using Dynamic Array which requires Excel 365`

or a shorter equivalent:

`=MIN(IF(WEEKDAY(DATE(SEQUENCE(10,1,2024,4),2,29), 2) = 6, DATE(SEQUENCE(10,1,2024,4),2,29)))`

### How does this work?

Dynamic Array is better illustrated by video.

If you like the video, give it a 👍 and subscribe my channel. 😉 An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged , , , , , , , . Bookmark the permalink.

### 2 Responses to Determine leap year with #Excel

1. Kanhaiyalal Newaskar. says:

Sir, 27th May,2020.
Very clearly and wisely shown method of proceeding formula.
I must thank you very much.
Hoping to receive more ideas in future too.
• MF says: