Excel Tip – What to do with Leap Year Babies!?

October 8, 2019

In a previous Tip we showed you the simple solution of extracting Birthdays from a Date of Birth List.

 

=Text(D4, “dd-MMM”) where D4 references a D.O.B. field.

 

The problem though is that this only works 99.9% of the time. For most that would be good enough, unless you’re a Leap Year Baby!

 

The issue is that on a Non-Leap Year, when you sort the Birthdays, the 29-Feb goes to the very bottom of the list, even after the 31-Dec. If you’re interested as to why this is so, see the explanation at the end of this post *.

 

The Solution is to use a slightly more complicated Nested DATE function, with a hardcoded leap-year such as 2000.

 

=DATE(2000,MONTH(D4),DAY(D4))

 

Apply a Custom Format of dd-MMM to remove the Year component and The Leap Year Birthday now takes its rightful place when sorted. 😊

 

*Text Function Explanation: When you extract the “dd-MMM” component from a D.O.B. to get a birthday, Excel recognizes this as a Date even though it is stored as Text. So when you click on sort, and you’re prompted to “Sort anything that looks like a Number as a Number”, Excel makes the assumption that the Date is from the current year and converts all the Dates to their numerical value in memory before applying the sort. The problem is that on a Non-Leap Year the Leap Year Date (e.g. 29-02-2019) does not exist and therefore cannot be converted to a Numerical Value. The 29-Feb then remains as Text for the sort and displays at the bottom after all the Numerical Dates.

 

 

Share on Facebook
Share on Twitter
Please reload

Featured Posts

Excel Tip – Using the Format Painter multiple times

November 20, 2019

1/1
Please reload

Recent Posts