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")
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.
I googled and found this:
https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html
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