Converting Excel time-format (hours since 1.1.1901)

[John Machin]

On Dec 8, 12:20 am, Dirk Hagemann <DirkHagem... at gmail.com> wrote:
> Hello,
>
> From a zone-file of a Microsoft Active Directory integrated DNS server
> I get the date/time of the dynamic update entries in a format, which
> is as far as I know the hours since january 1st 1901.

As Tim Golden has guessed, it is the number of hours since
1601-01-01T00:00:00. Weird but true. See (for example)
http://www.netpro.com/forum/messageview.cfm?catid=15&threadid=457

> For Example: the number 3566839 is 27.11.07 7:00.

Y2K bug! The number 3566839 is a representation of
2007-11-27T07:00:00.

> To calculate this in
> Excel I use this:
> ="01.01.1901"+(A1/24-(REST(A1;24)/24))+ZEIT(REST(A1;24);0;0)  (put
> 3566839 in field A1 and switch the format of the result-field to the
> corresponding date-time format).

"01.01.1901" => date(1901, 1, 1)

(A1/24-(REST(A1;24)/24)) => (A1/24-(MOD(A1,24)/24))
which simplifies to INT(A1/24)

ZEIT(REST(A1;24);0;0) => TIME(MOD(A1,24),0,0)

This is a convoluted way of writing DATE(1901, 1, 1) + A1 / 24

Your result is "correct" apart from the century. This is the result of
two canceling errors (1) yours in being 3 centuries out of kilter (2)
Microsoft's in perpetuating the Lotus 123 "1900 is a leap year" bug.

If you must calculate this in Excel, this formula might be better:

=DATE(2001, 1,  1) + A1 / 24 - 146097

(146097 is the number of days in a 400-year cycle, 400 * 365 + 100 - 4
+ 1)
>
> You might guess what I need now: I want to calculate this somehow in
> python.
>
> Sorry, but I couldn't find anything in the module time or something
> else to get this calculated.
>
> Does anyone know how to convert this time in python to something
> usable or how to convert this formula in python?
>

One very slight change to what Tim Golden suggested: make the result a
datetime, not a date.

>>> dnsdatetime2py = lambda x: datetime.datetime(1601,1,1,0,0,0) + datetime.timedelta(hours=x)
>>> dnsdatetime2py(3566839) # your example
datetime.datetime(2007, 11, 27, 7, 0)
>>> dnsdatetime2py(3554631) # example in cited web posting
datetime.datetime(2006, 7, 6, 15, 0)

HTH,
John