PDA

View Full Version : [SOLVED] Date conversion from Linux format



Blakieto
01-10-2005, 08:08 PM
Hi all,

I'm looking to see if anyone has already created a macro for conversion of linux timestamps into excel dates...

Linux times (as least on our linux network) is expressed as seconds since "00:00:00 Janurary 1, 1970".

Whereas Excel expresses dates as days since Janurary 1, 1900.

I'm working with database output from a linux server, and my dates are in Linux units.

Anyone done this already?

-Blakieto

Jacob Hilderbrand
01-10-2005, 08:13 PM
Can you post a workbook with some of the Linux outputs?

Jacob Hilderbrand
01-10-2005, 08:35 PM
This formula should work for this conversion.


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

Where A1 houses the Linux date in seconds.
A1/86400 will give the total number of days.
Then we add that to the start date of 1/1/1970.

Blakieto
01-11-2005, 12:08 PM
Interesting... I would have thought that DRJ's formula would work. However, it appears to be off by a fraction of a day.

I've attached a workbook with three columns: the provided linux timestamps, conversion via DRJ's formula and a column of the correct dates and times.

Blakieto
01-11-2005, 02:07 PM
By subtracting a half day from Jacob's formula, the correct answer is produced. The final formula is:

=DATE(1970,1,1) + (A1 - 43600)/86400

Thanks!

Jacob Hilderbrand
01-11-2005, 05:49 PM
I am not sure why you would have to suptract half a day unless the linux time started at 12PM on 1/1/1970?

Glad you got it working.

Take Care

Paleo
01-11-2005, 06:12 PM
Does Linux works always on that date system or is it a particular thing from this system??

Blakieto
01-11-2005, 06:41 PM
I believe that most linux systems use this time system. This is also called "coordinated universal time (UTC)". The linux command "localtime" receives time in these units and converts into human readable text. So it seems to be built in...

Blakieto
01-24-2005, 07:10 PM
After a bit of use, I find that what I posted as the correct formula was not correct. Rather than subtracting 12 hours during the conversion, there should be 8 hours subtracted:

=DATE(1970,1,1) + (A1 - 28800)/86400