java - How do I convert a Unix epoch timestamp into a human readable date/time in Excel?

Question

I have Excel documents containing Unix epoch timestamps from a Java application. I'd like to see what they translate to and represent them as human readable dates inside of Excel.

For example, the following long: 1362161251894 should evaluate to something readable like: 01 Mar 2013 11:07:31,894

I'm assuming I can create a formula for this, but I'm not sure how. Thanks!

question from:https://stackoverflow.com/questions/15164237/how-do-i-convert-a-unix-epoch-timestamp-into-a-human-readable-date-time-in-excel

Answer 1

Yes, you can create a formula to do this for you. Java and Unix/Linux count the number of milliseconds since 1/1/1970 while Microsoft Excel does it starting on 1/1/1900 for Windows and 1/1/1904 for Mac OS X. You would just need to do the following to convert:

For GMT time on Windows

=((x/1000)/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))

For GMT time on Mac OS X

=((x/1000)/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1904"))

For local time on Windows (replace t with your current offset from GMT)

=(((x/1000)-(t*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))

For local time on Mac OS X (replace t with your current offset from GMT)

=(((x/1000)-(t*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1904"))

---

In your specific case it looks like you are in a Mountain Time (GMT offset of 7). So if I paste your value given of 1362161251894 in a new Excel spreadsheet in cell A1 and then paste the following formula, I get a result of 41333.46356, which if I then tell Excel to format as a Date (press ctrl+1 on the cell) is: 2/28/13 11:07 AM

=(((A1/1000)-(7*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))