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
Depending on what type of export you do, the formula may or may not work in excel. If i export an Investigation Analysis and my time is like this: 1580064203, the formula works great. If i export another type of report and my time looks like: 1580064203000, the formula doesnt work. You need to get rid of the three 0's at the end using something like, =TEXT(B2/(60*60*24*1000)+DATE(1970,1,1),"yyyy/mm/dd hh:mm:ss") where the *1000 was added to knock off the last three 0's OR something like =(LEFT(B2,10)/86400) +DATE(1970,1,1)
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