Ok... this is one of the more 'geekier' posts I've written, but I was trying to convert a Unix epoch (the number of seconds from January 1, 1970) to a standard time using Microsoft Excel. I found a solution online, but it didn't work completely for me, so I thought I would add my solution here, mostly so that when I need to use it again, I'll be able to find it!
Searching Google, I had found this site which converted the epoch for me but I kept coming up with the wrong date and time... All the times were about four years in the future. Obviously, that was wrong, which I knew because I converted one of the times using another converter I had. The formula I was using was this:
=((epoch - 25200) / 86400) + 25569
Then I remembered, being on Mac OS, that day 0 is January 1, 1904. When I subtracted the extra four years from the formula, I got the right time. So, on a Mac, the correct formula is this:
=((epoch - 25200) / 86400) + 24106
So, here you go. The next time you are faced with using an epoch time and having to convert it to a standard time, you can use these formulas for whichever OS you are using.
Leave a Comment