Event time in CSV

[BrendanReid]

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

[JohnSnider · Accepted solution]

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

[BrendanReid]

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.

[DavidWaugh1]

I googled and found this:

https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html 

[JohnSnider]

That is odd, since I plugged in your number with that exact formula and got the proper output...

[GuyBruneau]

The command line with Unix date is date -d @1490232221 for Thu Mar 23 01:23:41 GMT 2017

[BrendanReid]

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

[ErnieCastro]

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)