Convert Excel 5 digit (serial) date numbers to Date

May 3, 2013 @ 12:34 - Michael

Categories: tech geek

I spent a lot of time trying to figure this out and found the easiest way to do it. Below is the detail.

Excel's serialized dates are the number of days since 1/1/1900. In order to figure out the date again, we have to add the serial number worth of days (subtracting one to account for the inability to not count 1/1/1900 as a day).

1 Original Date Serial Formula Converted Date
2 5/3/2013 41397 =DATE(1900,1,1)+B2-1 5/3/2013
3 5/3/2013 41397 =TEXT(B3,"mm/dd/yyyy") 5/3/2013