Date conversion from Linux format

[Blakieto]

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]

Can you post a workbook with some of the Linux outputs?

[Jacob Hilderbrand]

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]

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]

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]

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]

Does Linux works always on that date system or is it a particular thing from this system??

[Blakieto]

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]

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