Convert UNIX dates

  • Thread starter Thread starter vince
  • Start date Start date
V

vince

Does anyone have a ready-made formula to convert Unix
time stamps to our good ol human time?
Thx
 
Does anyone have a ready-made formula to convert Unix
time stamps to our good ol human time?
Click to expand...

Unix times should be the number of seconds from midnight GMT on 1 Jan 1970. If
you just want the Excel date and time for GMT, use

=DATE(1970,1,1)+X/86400

Adjustment to particular time zones requires adding or subtracting time from
GMT.
 
I've been working on this problem for some time now, and while this i
the most common formula, it is not 100% accurate (at least on m
system).

I use the following formula:
excel = ((unix - timezone) / 86400) + 25569
where:
unix = seconds since Jan 1, 1970, midnight, GMT
timezone = offset in seconds for my timezone (PST = 28800)
86400 = seconds in one day
25569 = Excel value for DATE(1970, 1, 1)

In my test, my unix GMT time for right now is 1070994879. Uni
interprets this as: Dec 9, 2003 10:34:39 PST. Inserting this same uni
time into the above formula results in an Excel time of 37964.4414062
or 12/9/2003 10:35:37. There is a difference of 58 seconds that
cannot explain. Unfortunately, my application needs to be exact to th
second.

Could this be a precision or roundoff error??? Other??

Notes:
- the formula is calculated in a Unix C program, all vars are long o
float as appropriate.
- Excel v.9 (2000)

-with thanks,
Mike
 
Oops! Issue solved.
It was precision after all. I changed my floats to doubles, and alas I
get a conversion accurate "to the second".
<sorry>.
 
Back
Top