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

java excel datetime excel-formula
Bryce picture Bryce · Mar 1, 2013 · Viewed 72.6k times · Source

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!

Answer

11101101b picture 11101101b · Mar 1, 2013

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

Related questions

java to excel date formatting

The problem is when I'm running my application and have a grid (with strings and date columns) and save it as an excel file. When I save it for the first time everything is correctly formatted, but when I try …

Read More
Reading time values from spreadsheet using poi api

I am trying to read a date column and a time column from a spreadsheet. I am able to retireve the date column from the sheet, but not the time column. For example, my sheet will have rows of the …

Read More
How to read and write excel file

I want to read and write an Excel file from Java with 3 columns and N rows, printing one string in each cell. Can anyone give me simple code snippet for this? Do I need to use any external lib or …

Read More