1. Registered User
Join Date
Apr 2004
Posts
8

Unix time stamp (a method of the showing date) is a umber of seconds from 1st of january 1970 to the present time.
How can it be done in Excel?
I mean in which way it should be converted from the figure (time stamp) to the date:hour format?
Any formula or something like that?
Thx for any help

2. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
#01-01-1970# + UnixTimeStamp / 60 / 60 / 24

/ 60 => To minutes
/ 60 => To hours
/ 24 => Days

that should work....

Regards

3. Registered User
Join Date
Apr 2004
Posts
8
Great thanx for the answer namliam

I think the problem is a bit deeper as every 4 years, the year is longer-366 days instead of 365
So a formula should be more complicated unfortunately.

4. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
No it should NOT! try it you will find it works ... this is because a date actually is a double, thus if unix counts seconds for a leap year it will count 60*60*24*366 seconds, where Excel will count just the 366 days.

Regards

5. Registered User
Join Date
Apr 2004
Posts
8
Wow-great thanx
One big reuest-can you say what exactly it needs to be put in the cell (how above formula gotta be written)?
I'm sorry-I am not too good in Excell
For example-let's say, my UnixTimeStamp value is in the cell B1
it should be
=B1 / 60 / 60 / 24 + what?

You are great-thx again for your time

6. Registered User
Join Date
Mar 2004
Location
Minnesota, USA, Earth
Posts
65

## Try...

Try...

=B1 /60/60/24 + DATE(1970,1,1)

I think this should work regardless if your system is using the 1900 Date System (Excel Default set-up) or the optional 1904 Date System.

-Mike

7. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
I KNOW it works... on every date system, tho you may have to change 1-1-1970 to 31-12-1969 depending on the exact setup of the timestamp...

8. Registered User
Join Date
Apr 2004
Posts
8
It works like a charm.
Great thanx for your help guys.
It covers my axx at work
Thx again

9. Registered User
Join Date
Mar 2004
Location
Minnesota, USA, Earth
Posts
65

## Credit to namliam

Props to namliam for his knowledge. I was just around to add a DATE formula.

10. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
Now if only someone would help me , but my problems seem to stump everyone...

Regards

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•