Converting Excel time-format (hours since 1.1.1901)

[mensanator at aol.com]

On Dec 10, 3:49 am, Dirk Hagemann <DirkHagem... at gmail.com> wrote:
> 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".

I apologize for the snide tone. But the reality is that
you DO belong to everyone as how Excel calculates time
in the background is explicitly stated in the Help files.

> 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.

I wasn't trying to assign blame. There's a computer term
called GIGO, it stands for Garbage In, Garbage Out. It means
that even if your formulae are correct, the result will be
no better than the input, bad input produces bad output and
the computer has no way to tell this. It is the programmer's
responsibility to verify consistency. The magnitude of the
number is inconsitent with Excel time formats. That's a clue
that you can't use Excel date functions directly on this number.
It is also inconsistent with hours from 1901 as it would be
off by 4 centuries. That's a clue that either the formula
is wrong or your interpretation of it is wrong.

Once you have all the wrinkles ironed out, it will then
become clear how to convert this number to it's equivalent
Excel format so that you CAN use Excel date functions if
desired.