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

 

[agilder]

This is a tough one chap.

Just as a pointer and you have probably already thought of this - Don't forget to acocunt for:

1. Leap years
2. Geographical time zones
3. Day light saving

In total these factors could account for upto a 10 day error!

I would love to know the solution! Sorry I cannot be of more help!

Andrew

 

[Yogi Anand]

Hi Dave:

Let us have a look at ...

Book1
	A	B	C	D
1
2	UnixTime	SecondsFromDec-31-1899MidNightto1/1/1970Midnight	ExcelDateTime
3	1096405159	2209161600	Sep-28-200420:59:19
4	1110585159	2209161600	Mar-11-200523:52:39
5
Sheet3

I hope this helps!

Please note based on Unix Time you gave I compute it as Sep-28-2004 20:59: ...

 

[Yogi Anand]

Hi Dave:

The HTML post I have above doesn't look too cool. So let me try again ...

ExcelTime is = UnixTime + ("1/1/1970"-"1/1/1900"+1)*86400

and then I formated the resulting entry as mmm-dd-yyyy hh:mm:ss

I hope this helps!

 

[Pekkavee]

Have a look in here
http://www.sins.com.au/unix/unix-timestamp.html and here
http://www.unixtimestamp.com/

Pekka

 

[agilder]

Yogi,

Shouldn't the equation be:

ExcelTime is = (UnixTime + ("1/1/1970"-"1/1/1900"+1)*86400) / 86400

Regards

Andrew

 

[Yogi Anand]

Hi Andrew:

Yes Sir -- sorry for the editorial oversight ... the equation should correctly read ...

ExcelTime is = (UnixTime + ("1/1/1970"-"1/1/1900"+1)*86400) / 86400

 

[agilder]

No worries. I was interested to understand your solution.

Have a good week.

Andrew

 

[dhcrusoe]

Thanks!

Thanks! Everything works, though I had to subtract ~4 hours from the result to match the Unix code...

 

[Haydo69]

Hi Andrew:

Yes Sir -- sorry for the editorial oversight ... the equation should correctly read ...

ExcelTime is = (UnixTime + ("1/1/1970"-"1/1/1900"+1)*86400) / 86400

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!