On Dec 10, 3:49 am, Dirk Hagemann <[EMAIL PROTECTED]> wrote: > On 9 Dez., 18:38, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > > > > > On Dec 9, 8:52�am, Dirk Hagemann <[EMAIL PROTECTED]> wrote: > > > > On 7 Dez., 22:36, John Machin <[EMAIL PROTECTED]> wrote: > > > > > On Dec 8, 12:20 am, Dirk Hagemann <[EMAIL PROTECTED]> 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. -- http://mail.python.org/mailman/listinfo/python-list
