Hi there.
Looking for a way to convert the CSV exported Event Time value of 1490232221 back into 2017-03-22T18:23:41 or equivalent in Excel.
Note:
For Excel 2016, use the following:
=TEXT(A1/(60*60*24)+DATE(1970,1,1),"yyyy/mm/dd hh:mm:ss")
Hi there.
Looking for a way to convert the CSV exported Event Time value of 1490232221 back into 2017-03-22T18:23:41 or equivalent in Excel.
Note:
For Excel 2016, use the following:
=TEXT(A1/(60*60*24)+DATE(1970,1,1),"yyyy/mm/dd hh:mm:ss")
you can add a formula to do that:
=TEXT(A1/(60*60*24)+"1/1/1970","yyyy/mm/dd hh:mm:ss")
Where A1 is the cell# of the Unix Epoch date
Thanks for responding John.
When I use that formula I get a #VALUE error.
If I remove +"1/1/1970" I get 1947/03/22 05:37:25, which is of course off by 70 years.
I'll keep working with it, thanks for getting me on the right track.
That is odd, since I plugged in your number with that exact formula and got the proper output...
Excel 2007 provides the result as expected.
Excel 2016 gives me a #VALUE error.
Curse you Microsoft. Will update this post when I figure out the issue.
Update:
Using Date(1970,1,1) as part of the formula resolved the issue with Excel 2016.
=TEXT(A1/(60*60*24)+DATE(1970,1,1),"yyyy/mm/dd hh:mm:ss")
The command line with Unix date is date -d @1490232221 for Thu Mar 23 01:23:41 GMT 2017
you can add a formula to do that:
=TEXT(A1/(60*60*24)+"1/1/1970","yyyy/mm/dd hh:mm:ss")
Where A1 is the cell# of the Unix Epoch date