Converting unix timestamp to excel date-time...

[dhcrusoe]

Hey all,

This is a relatively simple problem, I think, but it's driving me crazy. I'm analyzing 13.8 million rows of data (participant observation in an educational MUVE) and need to convert the unix timestamp to a date/time.

I know that, for example:

Unix stamp: 1096405159
converts to: Thu 10/07/04 sometime around 9.00 am...

but my formula:

=(F2+((365*70+19)*86400))/86400-0.41667 isn't working right... any suggestions?

Thanks!

--dave

 

[Pissot]

Hello guys,

I guess you overcomplicate the problem.
I keep following page in bookmarks for cases when I need to convert timestamp to date in excel
Hope, it will be usefull for you

Best wishes

 

[Ticcer]

Yes, that's perfect if you live on the Greenwich Mean Time Line...

To add GMT offset, timezone is required in the formula:
=(D17591 + ("1/1/1970"-"1/1/1900"+1)*86400 +36000) / 86400
=(M$_XL_Cell_Value + ("1/1/1970"-"1/1/1900"+1)*86400 + GMT_Offset) / 86400
36000 = GMT+10
Aussie Aussie Aussie, Oi Oi Oi!

I'm in Brisbane (GMT+10) I'm having to use the following formula to get accurate dates and times with data exported from GO SMS Pro

Epoch Date in cell D2 = 1431242184520

Formula in F2: =(D2/1000)/86400+25569+0.416666666667 - Gives the result 42134.7197282407

To convert to Readable date and time, 2 columns

=TEXT($F2,"dd/mm/yyyy") = 10/05/2015

=TEXT($F2,"hh:mm:ss") = 17:16:25

These dates and times are correct when I compare them to the SMS app on my phone.

However:

When I use the formula you describe, e.g. =(D2+("1/1/1970"-"1/1/1900"+1)*86400+36000)/86400 I get the result of 16590872.4782407... nowhere near the 42134.7197282407 I require.

I probably just don't understand your formula, but it would be nice to know what's going on.

Cheers,

Ticcer