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]

Re: Converting Excel time-format (hours since 1.1.1901)

On Dec 7, 7:20 am, Dirk Hagemann <DirkHagem...@g mail.comwrote:

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:

>>import xlrd
>>xlrd.xldate.x ldate_as_tuple( 38980,0)

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

>>>

chad!

[Dirk Hagemann]

Re: Converting Excel time-format (hours since 1.1.1901)

On 7 Dez., 14:34, supercooper <supercoo...@gm ail.comwrote:

On Dec 7, 7:20 am, Dirk Hagemann <DirkHagem...@g mail.comwrote:
>
>
>

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

>import xlrd
>xlrd.xldate.xl date_as_tuple(3 8980,0)

>
(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.xld ate_as_tuple(35 66985,0)
File "C:\Python25\li b\site-packages\xlrd\x ldate.py", line 75, in
xldate_as_tuple
raise XLDateTooLarge( xldate)
xlrd.xldate.XLD ateTooLarge: 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@aol.com]

Re: Converting Excel time-format (hours since 1.1.1901)

On Dec 7, 7:20�am, Dirk Hagemann <DirkHagem...@g mail.comwrote:

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

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]

Re: Converting Excel time-format (hours since 1.1.1901)

On Dec 7, 8:15 am, Dirk Hagemann <DirkHagem...@g mail.comwrote:

On 7 Dez., 14:34, supercooper <supercoo...@gm ail.comwrote:
>
>
>

On Dec 7, 7:20 am, Dirk Hagemann <DirkHagem...@g mail.comwrote:

>

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:

>

>>import xlrd
>>xlrd.xldate.x ldate_as_tuple( 38980,0)

>

(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.xld ate_as_tuple(35 66985,0)
File "C:\Python25\li b\site-packages\xlrd\x ldate.py", line 75, in
xldate_as_tuple
raise XLDateTooLarge( xldate)
xlrd.xldate.XLD ateTooLarge: 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]

Re: Converting Excel time-format (hours since 1.1.1901)

On 7 Dec, 13:20, Dirk Hagemann <DirkHagem...@g mail.comwrote:

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

"""

>>import datetime
>>timezero=date time.datetime(1 901, 1, 1)
>>timezero+date time.timedelta( hours= 3566839)

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

Giles

[Tim Golden]

Re: Converting Excel time-format (hours since 1.1.1901)

mensanator@aol. com wrote:

On Dec 7, 7:20�am, Dirk Hagemann <DirkHagem...@g mail.comwrote:

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

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

<code>
import datetime
print datetime.date (1901, 1, 1) + datetime.timede lta (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.timede lta (hours=3566839)

</code>

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

TJG

[Dirk Hagemann]

Re: Converting Excel time-format (hours since 1.1.1901)

On 7 Dez., 16:21, Tim Golden <m...@timgolden .me.ukwrote:

mensana...@aol. com wrote:

On Dec 7, 7:20�am, Dirk Hagemann <DirkHagem...@g mail.comwrote:

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.

>
If it *is* then the easiest way is this:
>
<code>
import datetime
print datetime.date (1901, 1, 1) + datetime.timede lta (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.timede lta (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]

Re: Converting Excel time-format (hours since 1.1.1901)

On 7 Dez., 16:50, Dirk Hagemann <DirkHagem...@g mail.comwrote:

On 7 Dez., 16:21, Tim Golden <m...@timgolden .me.ukwrote:
>
>
>

mensana...@aol. com wrote:

On Dec 7, 7:20�am, Dirk Hagemann <DirkHagem...@g mail.comwrote:
>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.

>

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

>

<code>
import datetime
print datetime.date (1901, 1, 1) + datetime.timede lta (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.timede lta (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

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]

Re: Converting Excel time-format (hours since 1.1.1901)

On 7 Dez., 16:50, Dirk Hagemann <DirkHagem...@g mail.comwrote:

On 7 Dez., 16:21, Tim Golden <m...@timgolden .me.ukwrote:
>
>
>

mensana...@aol. com wrote:

On Dec 7, 7:20�am, Dirk Hagemann <DirkHagem...@g mail.comwrote:
>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.

>

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

>

<code>
import datetime
print datetime.date (1901, 1, 1) + datetime.timede lta (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.timede lta (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

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

[Zentrader]

Re: Converting Excel time-format (hours since 1.1.1901)

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]

Re: Converting Excel time-format (hours since 1.1.1901)

Dirk Hagemann <DirkHagemann@g mail.comwrote:

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

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]

Re: Converting Excel time-format (hours since 1.1.1901)

Dirk Hagemann wrote:

(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@aol.com]

Re: Converting Excel time-format (hours since 1.1.1901)

On Dec 7, 9:59 am, Dirk Hagemann <DirkHagem...@g mail.comwrote:

On 7 Dez., 16:50, Dirk Hagemann <DirkHagem...@g mail.comwrote:
>
>
>
>
>

On 7 Dez., 16:21, Tim Golden <m...@timgolden .me.ukwrote:

>

mensana...@aol. com wrote:
On Dec 7, 7:20�am, Dirk Hagemann <DirkHagem...@g mail.comwrote:
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.

>

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

>

<code>
import datetime
print datetime.date (1901, 1, 1) + datetime.timede lta (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.timede lta (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

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

Re: Converting Excel time-format (hours since 1.1.1901)

On Dec 8, 12:20 am, Dirk Hagemann <DirkHagem...@g mail.comwrote:

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.

>>dnsdatetime2p y = lambda x: datetime.dateti me(1601,1,1,0,0 ,0) + datetime.timede lta(hours=x)
>>dnsdatetime2p y(3566839) # your example

datetime.dateti me(2007, 11, 27, 7, 0)

>>dnsdatetime2p y(3554631) # example in cited web posting

datetime.dateti me(2006, 7, 6, 15, 0)

HTH,
John