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!

Featured Posts
Posts Are Coming Soon
Stay tuned...
Recent Posts
Search By Tags

Training Offices:


106 Johan Avenue, Sandton


1 Tamarind Close, Umhlanga


400 Old Howick Road, Hilton

Corporate Onsite Training Available Countrywide

  • Summit Solutions Excel Training
  • Summit Solutions Excel Training
  • Summit Solutions Excel Training

Tel: 086 167 3923

Email: info@summitsolutions.co.za 

Head Office: Hilton Quarry Office Park, 400 Old Howick Road, Hilton.

2020 | Copyright | Summit Solutions