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 Wikipedia.  Below 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 onward
Note: 
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. 😉