Converting Excel time-format (hours since 1.1.1901)

[Dirk Hagemann]

On 9 Dez., 18:38, "mensana... at aol.com" <mensana... at aol.com> wrote:
> On Dec 9, 8:52�am, Dirk Hagemann <DirkHagem... at gmail.com> wrote:
>
>
>
> > 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...
>
> Everyone knows. Excel assumes an integer is
> DAYS SINCE 1900 and all it's calculations
> are based on that assumption.
>
> It's YOUR fault if you give Excel an integer
> that represents HOURS SINCE 1601, so don't
> expect meaningful calculations from Excel if
> you give it an incorrect data type.
>
>
>
> > Enjoy the sunday and have a great week!
> > Dirk

Sorry, but then I seem not to belong to "everyone". And it was not me
who created this Excel-formula, I just posted it as a kind of help.
And actually I just asked if somebody knows something about this time-
format and how to convert it. I think I already wrote that I did a
mistake and not Excel.