Converting Excel time-format (hours since 1.1.1901)

[Dirk Hagemann]

On 7 Dez., 22:36, John Machin <sjmac... at lexicon.net> wrote:
> 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
> >ExcelI 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 inExcel, 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

YES - that's it!
Thanks a lot to John, Tim and all the others who helped me to handle
this time format!!!

I was irritated by the date of 01.01.1901 in the Excel formula, but in
the end it was obvious that it has to be hours since 1601. Who knows
how Excel calculates in the background...

Enjoy the sunday and have a great week!
Dirk