Gizmokid2005, Organized Chaos!


Convert Excel 5 digit (serial) date numbers to Date

Posted on Friday, May 3rd, 2013 at 12:34 PM in geek, tech by Michael

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

 

  • AAS

    I used your formula but add small correction and that( -2) instead of (-1)

  • Piet

    Apparently it was thought by Excel designers that 1900 was a leap year, therefore -2 is needed

  • Darren

    The second one should be
    =TEXT(B3,”mm/dd/yyyy”)

    • You would be correct, that’s a typo on my part! Updated, thanks!

      • Darren

        Not a problem Michael, I’m coming back to look this up again in 2 weeks xD
        Forgetting things quickly

        • Haha, I hear that! I’m just glad it could lend a hand to someone 🙂