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". 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. -- http://mail.python.org/mailman/listinfo/python-list