Gizmokid2005, Organized Chaos!


Posts Tagged ‘Convert’

Convert Excel 5 digit (serial) date numbers to Date

Friday, May 3rd, 2013

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).

Quick Date Convert
A B C D
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