Today I have been given the task of sorting a spreadsheet with about 20,000 rows by birthdate or anniversary.  The sort needs to be by month and day, ignoring the year for CRM.  The simple formula I used was TEXT(CELL,”mm/dd”).  The problem was that many (thousands) of the dates were in unrecognizable formats such as these examples:
September 11th
April 1st
3/3/740
etc.

Excel did not recognize what to do with these as they were not dates.  I’m working on something that will convert invalid dates to valid month and day if possible.

I’ll update this soon.

This seems to do well for most cases.  It assumes the source is in L2:
=IF(LEFT(L2,1)>”9″,TEXT(MATCH(LEFT(L2,3),{“jan”,”feb”,”mar”,”apr”,”may”,”jun”,”jul”,”aug”,”sep”,”oct”,”nov”,”dec”},0),”00″)&”/”&TEXT(MID(L2,LEN(L2)-3,2),”00″),TEXT(L2,”mm/dd”))

Substitute the L2 with the source cell.

---

Posted

December 31, 2013

in

Excel

by

Dennis Walker