Happy Leap Day ! (★★)

Happy Leap Day ! (★★)

Hello everyone, it is 29th February ! What makes this day so special ?

[You can download here the example workbook]

A bit of history and physics

A leap year has 366 days instead of 365 – the 29th February is the famous “Leap Day”. With a few exceptions, it occurs every 4 years. It was introduced in 46 Before Christ by Jules Cesar to correct the errors of the previous calendar (Julian calendar). Indeed, Earth’s cycle (the time required for our planet to make a full revolution around the sun)  is precisely 365.2425 days.

In the Julian calendar, a leap year was used every 4 years to correct the error induced by 3 normal (and short) years (with 365 days). The average year was then 365.25 days, which used to generate 8 more days every 1,000 years. In 1582, Pope Gregory XIII decided to add a rule (Gregorian calendar) in order to refine this approximation.

The new rule

Here is the current rule to determine whether a year is a leap year or not :

  • The year should be divisible by 4 and not by 100

or

  • The year should be divisible by 400

So for example, 2012, 2016 or 2020 are leap years. But 1900, 2017 or 2100 are not.

For those who are just lazy to count or for sports amateurs, leap years will always coincide with summer Olympic Games or Euro football championship (excluding very few exceptions).

And in Excel ?

How to translate this rule into Excel ?

Supposing you have a year in the cell C2 of your Excel sheet. You could combine logic functions to translate this rule into “Excel language”. The formula would be :

=OR(AND(MOD(C2,4)=0,MOD(C2,100)<>0),MOD(C2,400)=0)

Tired of “pure” mathematics ? Let’s skip it for a couple of minutes and use Excel date functions to solve the problem :

=DAYS(DATE(C2,12,31),DATE(C2,1,1))=365

=DAY(EOMONTH(DATE(C2,2,1),0))=29

would both do the job.

Please note that days (count the days between 2 dates) is an interval function and it is only available from Excel 2013.

= Days (12/01/2016, 10/01/2016) would return 2 for example

So in our Excel example, a whole leap year has to have 365 days if we are using Days function.

Download the example workbook !

Did you know ?

Excel incorrectly assumes 1900 is a leap year. It helped Microsoft handle with leap years and date calculations when interacting with Lotus 1-2-3. The company could easily cope with this issue now but the disadvantages of doing so outweigh the advantages. Indeed, it would for example break date compatibility between Excel and other programs.

To be honest, this is just a matter of details. Would you seriously use the year 1900 in your Excel spreadsheets ? And Excel correctly handles all other leap years.

If you have downloaded the example workbook, you must have noticed that a conditional formatting is applied on the years. We will see soon how to use this powerful Excel functionality.

Interested in this article ? Please feel free to share, like or comment.

You can also subscribe to the blog so you can keep up with the last news !

★ Beginner    ★★ Intermediary     ★★★ Advanced

 

 

 

Advertisements

4 thoughts on “Happy Leap Day ! (★★)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s