Converting Excel time-format (hours since 1.1.1901)

[Dirk Hagemann]

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.
For Example: the number 3566839 is 27.11.07 7:00. To calculate this in
Excel I 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).

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?

Thanks a lot and regards
Dirk

 

[supercooper]

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.
For Example: the number 3566839 is 27.11.07 7:00. To calculate this in
Excel I 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).

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?

Thanks a lot and regards
Dirk

I think you want the xldate_as_tuple function in the xlrd module:

http://www.lexicon.net/sjmachin/xlrd.htm

It works like a champ for me:

chad!

 

[Dirk Hagemann]

I think you want the xldate_as_tuple function in the xlrd module:

http://www.lexicon.net/sjmachin/xlrd.htm

It works like a champ for me:


(2006, 9, 20, 0, 0, 0)



chad!

Thanks so far, that comes close to a solution I think, BUT when I
enter 3566985 instead of 38980 I get the following error:
Traceback (most recent call last):
File "test.py", line 20, in <module>
print xlrd.xldate.xldate_as_tuple(3566985,0)
File "C:\Python25\lib\site-packages\xlrd\xldate.py", line 75, in
xldate_as_tuple
raise XLDateTooLarge(xldate)
xlrd.xldate.XLDateTooLarge: 3566985

Do I have to use another function of this module? My number is 2
digits shorter than yours. What is 38980 representing?

Dirk

 

[mensanator]

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.

Your guess appears to be off by a couple centuries.

(3566839/24)/365 = 407

 

[supercooper]

Thanks so far, that comes close to a solution I think, BUT when I
enter 3566985 instead of 38980 I get the following error:
Traceback (most recent call last):
File "test.py", line 20, in <module>
print xlrd.xldate.xldate_as_tuple(3566985,0)
File "C:\Python25\lib\site-packages\xlrd\xldate.py", line 75, in
xldate_as_tuple
raise XLDateTooLarge(xldate)
xlrd.xldate.XLDateTooLarge: 3566985

Do I have to use another function of this module? My number is 2
digits shorter than yours. What is 38980 representing?

Dirk

Excel stores dates as floating point
numbers; the number of days (or fraction thereof) since
12/31/1899. So 38980.0 is midnight 9/20/2006. You think your numbers
represent
the number of hours since 1/1/1901?

3,566,985 hrs/24 hrs in a day = ~148,618 days
148,618 days/365 days in a year = ~407 years

Am I doing the math wrong?

 

[Giles Brown]

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.
For Example: the number 3566839 is 27.11.07 7:00. To calculate this in
Excel I 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).

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?

Thanks a lot and regards
Dirk

Something is a bit off here...

"""datetime.datetime(2307, 11, 27, 7, 0)
"""

Giles

 

[Tim Golden]

If it *is* then the easiest way is this:

<code>
import datetime
print datetime.date (1901, 1, 1) + datetime.timedelta (hours=3566839)

</code>

But, as someone pointed out, that puts you somewhere in 2300.
Where are you getting the 1901 from (and the hours, for that
matter). If it's based, as AD dates are, for example, from 1601,
then the calc becomes:

<code>
import datetime
print datetime.date (1601, 1, 1) + datetime.timedelta (hours=3566839)

</code>

which looks more realistic. But frankly I'm guessing.

TJG

 

[Dirk Hagemann]

If it *is* then the easiest way is this:

<code>
import datetime
print datetime.date (1901, 1, 1) + datetime.timedelta (hours=3566839)

</code>

But, as someone pointed out, that puts you somewhere in 2300.
Where are you getting the 1901 from (and the hours, for that
matter). If it's based, as AD dates are, for example, from 1601,
then the calc becomes:

<code>
import datetime
print datetime.date (1601, 1, 1) + datetime.timedelta (hours=3566839)

</code>

which looks more realistic. But frankly I'm guessing.

TJG

(3566839/24)/365 = 407 - YES I did this calculation too and was
surprised. But if you try this out in MS Excel:
="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-fieldby right-
click on it to the
date format "14.3.01 13:30")

and then replace 3566839 by, let's say, "2", Excel calculates the date
01.10.1901 2:00 AM. Try out other values like 5 or 24! So I thought
3566839 represents hours.

Dirk

 

[Dirk Hagemann]

(3566839/24)/365 = 407 - YES I did this calculation too and was
surprised. But if you try this out in MS Excel:
="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-fieldby right-
click on it to the
date format "14.3.01 13:30")

and then replace 3566839 by, let's say, "2", Excel calculates the date
01.10.1901 2:00 AM. Try out other values like 5 or 24! So I thought
3566839 represents hours.

Dirk

Additional to my last posting: if you want to try this out in Excel
you should replace the command "REST" by the english command what
should be something like "remainder".

 

[Dirk Hagemann]

(3566839/24)/365 = 407 - YES I did this calculation too and was
surprised. But if you try this out in MS Excel:
="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-fieldby right-
click on it to the
date format "14.3.01 13:30")

and then replace 3566839 by, let's say, "2", Excel calculates the date
01.10.1901 2:00 AM. Try out other values like 5 or 24! So I thought
3566839 represents hours.

Dirk

Oh - sorry again: in the Excel formula replace also ZEIT with TIME.

 

[Zentrader]

If we use minutes from 2001, then 3566839 comes out as sometime in
October, 2007 (6.78622 years). Close but no cigar. Is anyone familar
enough with Excel to translate the formula or do we have to go a-
googling?

 

[Max Erickson]

Additional to my last posting: if you want to try this out in
Excel you should replace the command "REST" by the english
command what should be something like "remainder".

The equivalent in my (U.S. English, 2000) version of excel is called
'MOD'.

Also, you have misread or miscopied something, or are encountering
some very strange issue, as when I put your formula in excel, I get
the following output:

11/27/2307 7:00


max

 

[Boris Borcic]

(3566839/24)/365 = 407 - YES I did this calculation too and was
surprised. But if you try this out in MS Excel:
="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-fieldby right-
click on it to the
date format "14.3.01 13:30")

and then replace 3566839 by, let's say, "2", Excel calculates the date
01.10.1901 2:00 AM.

Hum, how can it be that Excel changes from YY to YYYY year display format ? What
does it display in the first case with a YYYY display format ?

 

[mensanator]

Oh - sorry again: in the Excel formula replace also ZEIT with TIME

Also, Excel unformatted dates are DAYS, not hours. And it's
from 1900, not 1901. Hours are always fractional parts:

1/1/01 0:00 367
1/1/01 12:00 367.5

It sure sounds like the number being given you ISN'T the
same as Excel date serial numbers.

 

[John Machin]

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
Excel I 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 in Excel, 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.
datetime.datetime(2006, 7, 6, 15, 0)

HTH,
John

 

[Dirk Hagemann]

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


Y2K bug! The number 3566839 is a representation of
2007-11-27T07:00:00.


"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)






One very slight change to what Tim Golden suggested: make the result a
datetime, not a date.


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

 

[mensanator]

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.

 

[Dirk Hagemann]

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.

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.

 

[mensanator]

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.