Excel Tip – Extracting a Birthday from a Date of Birth
Ever had issues when trying to Sort Birthdays in the correct order?
Changing the Format of a D.O.B to dd-MMM doesn’t work. When you sort, it still orders by Birth Date (age) and not Birth Day because the Year component still exists even though it does not display.
The simplest solution is to extract just the Day and Month component by using the Text Function. E.g. =Text(E2, “dd-MMM”) where E2 references a D.O.B. field.
Even though this is now a Text Field (auto-aligns to the left), because Excel recognises the Day-Month combination, it assumes the current year (just like when you type a “year-less” date) and therefore thinks you have a Date stored as Text. So, when you use the Custom Sort you should be prompted to…
“Sort anything that looks like a number, as a number”. Select this option and you have your list in Birthday order!