Converting unix timestamp to excel date-time...

A

agilder

New Member

Joined

Feb 23, 2005

Messages

42

• Mar 11, 2005

• #2

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

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Mar 11, 2005

• #3

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: ...

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Mar 11, 2005

• #4

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!

 

Upvote 0

P

Pekkavee

Well-known Member

Joined

May 25, 2004

Messages

1,218

• Mar 12, 2005

• #5

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

Pekka

 

Upvote 0

A

agilder

New Member

Joined

Feb 23, 2005

Messages

42

• Mar 12, 2005

• #6

Yogi,

Shouldn't the equation be:

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

Regards

Andrew

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Mar 12, 2005

• #7

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

 

Upvote 0

A

agilder

New Member

Joined

Feb 23, 2005

Messages

42

• Mar 13, 2005

• #8

No worries. I was interested to understand your solution.

Have a good week.

Andrew

 

Upvote 0

D

dhcrusoe

New Member

Joined

Jan 10, 2005

Messages

11

• Mar 21, 2005

• #9

Thanks!

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

 

Upvote 0

H

Haydo69

New Member

Joined

Sep 29, 2013

Messages

1

• Sep 29, 2013

• #10

Yogi Anand said:

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

Click to expand...

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!

 

Upvote 0