Technology seems to run on epoch, and that’s fine unless you need a quick and dirty way to get a date – especially in windows. Working with epoch dates, sometimes feels like decoding the matrix. I had a report that had a ton of epoch and I needed to convert to real dates – not too hard in perl or shell or etc… but I’m in Excel on Windows and I didn’t really want to run it through a filter so here’s the equation…
((epoch – timezone adjustment) / seconds in a day) + (days between 1/1/1900 and 1/1/1970)
It looks a little more like this in Excel: (for MST anyway)
=((date – 25200) / 86400) + 25569
I really don’t understand why Excel has to be different, It would be great if I could have just formatted the column as a date – oh well, at least it wasn’t too hard to figure out.
-Peter
October 24th, 2006 at 9:50 am
[...] http://www.mrexcel.com/archive2/44100/51255.htm http://www.peterabowen.com/?p=8 [...]
June 3rd, 2007 at 7:44 am
I found that you need to add 24106 in my Mac version of Excel to get the correct date/time. My formula is:
=((K2 – (25200)) / 86400) + 24106
The difference is that Mac OS starts on January 1, 1904, so you need to subtract a few less days.
June 3rd, 2007 at 7:51 am
[...] I had found this site which converted it but I kept coming up with the wrong date and time… All the times were four years in the future. Obviously, that was wrong. The formula I was using is this: [...]
March 11th, 2008 at 7:24 am
Hello Peter, could you help me figure out the Time Zone adjustment for Eater Time, Im in Florida. Not sure what to use to calculate this. The formula would be nice. Thx
September 12th, 2008 at 12:41 am
Thank you! You’ve saved me a lot of time and trouble.
February 24th, 2009 at 10:07 pm
Peter, you’re a legend! I have a HUGE wad of information that I need to graph after importing into Excel.
February 24th, 2009 at 11:11 pm
BTW for anyone wondering what the adjustment figure is for Australian (AEDT) = -39600
So the formula as provided by Peter, becomes:
=(( – -39600) / 86400) + 25569