Time Conversion

[JasonDW]

This may be a noob question but I did try searching the forums before posting.

I am trying to handle a time reported within a text file. The time is reported in the format 39876.7707 (equivalent to 3/4/2009 18:30). Are there any AutoIT functions that deal with this?

Thanks in advance.

Edited March 5, 2009 by JasonLA

[weaponx]

Oh this one is simple. Thats the long forgotten elven date format, once used by the elders in Rivendell. Times change though and the elves have adapted to the now preferred U.S. formatting.

Seriously what format is that?

[Authenticity]

More interestingly is how old is the date it's according to?? like 1\1\1970? or 1\1\1700?

[Merckie]

I think it's the number of days since 1/1/1900. The fractional number will be the time, the whole number the date. You can probably use the _DateAdd function.

[JasonDW]

I know that at minimum it is how a date is recorded in excel spreadsheets. If you format a dated cell as general it will show this type of number. It may be an offset from 1970 and I could probably create a function to work it out but I figured I should ask if there is something out there already.

The text file this is coming from is a picasa album (example attached)..can be opened in textpad or similar. (or maybe not...I couldn't upload it).

The file is like this:

<picasa2album>

<DBID>969ed6c9112b9b24184cfed4ca7ba8fd</DBID>

<AlbumID>07f983a584e7f44c36f3a3fb57254c1b</AlbumID>

<property name="uid" type="string" value="07f983a584e7f44c36f3a3fb57254c1b"/>

<property name="token" type="string" value="]album:07f983a584e7f44c36f3a3fb57254c1b"/>

<property name="name" type="string" value="Digital Frame"/>

<property name="hascollage" type="flag" value="0"/>

<property name="date" type="real64" value="39876.813924"/>

<property name="category" type="num" value="0"/>

<files>

<filename>$My Pictures\2009-02-07 Willow Springs Track Day\DSC05187.JPG</filename>

<filename>$My Pictures\2009-02-07 Willow Springs Track Day\DSC05188.JPG</filename>

<filename>$My Pictures\2009-02-07 Willow Springs Track Day\DSC05195.JPG</filename>

<filename>$My Pictures\2009-02-07 Willow Springs Track Day\DSC05198.JPG</filename>

</files>

</picasa2album>

Edited March 5, 2009 by JasonLA

[PsaltyDS]

This may be a noob question but I did try searching the forums before posting.

I am trying to handle a time reported within a text file. The time is reported in the format 39876.7707 (equivalent to 3/4/2009 18:30). Are there any AutoIT functions that deal with this?

Thanks in advance.

Well, I thought it looked like a DOS Date/Time, but this code:

#Include <Date.au3>
ConsoleWrite("Date/Time = " & _Date_Time_DOSDateTimeToStr(39876, 7707) & @LF)

...gives this output:

Date/Time = 04/14/1993 03:48:54

You'll have to explain more where that format came from.

[Authenticity]

Yeah, Merckie is right.

The decimal number is the days passed since 1\1\1970 and the fractional is something relating to it at the minute level I believe.

Edit: Nope, you're right, it's since 1900 and it's the days.

Edited March 5, 2009 by Authenticity

[Merckie]

The file looks like XML so you may be able to use _XMLDOMWrapper to extract the date/time string.

#Include <Date.au3>
Local $DatePart, $TimePart

$text = 39876.7707
$Date = _DateAdd("D",Floor($text),"1900/01/01 00:00:00")
$Date = _DateAdd("n",Round(($text-Floor($text))*24*60),$Date)
_DateTimeSplit($Date, $DatePart, $TimePart)
$Date = $DatePart[2] & "/" & $DatePart[3] & "/" & $DatePart[1] & " " & $TimePart[1] & ":" & $TimePart[2]
MsgBox(0,"",$Date)

[PsaltyDS]

Yeah, Merckie is right.

The decimal number is the days passed since 1\1\1970 and the fractional is something relating to it at the minute level I believe.

Edit: Nope, you're right, it's since 1900 and it's the days.

Well, something's still funky... this code:

#Include <Date.au3>

$sDateTime = _DateAdd("D", 39876, "1900/01/01 00:00:00")
$iSec = Int((60 * 60 * 24) * .7707)
$sDateTime = _DateAdd("S", $iSec, $sDateTime)
ConsoleWrite("$sDateTime = " & $sDateTime & @LF)

Comes up about two days off with this output:

$sDateTime = 2009/03/06 18:29:48

So, maybe this thing doesn't do leap days right?

[JasonDW]

Ok...I will try that code .... I did a bit more digging and

a) the full number was actually 39876.7707048611

0.0 is 1/0/1900 12:00:00 AM

The code works great!! Thanks Merckie...I am amazed there isn't a UDF out there since this is definitely how excel saves dates.

Thanks again everyone.

[PsaltyDS]

Ok...I will try that code .... I did a bit more digging and

a) the full number was actually 39876.7707048611

0.0 is 1/0/1900 12:00:00 AM

The code works great!! Thanks Merckie...I am amazed there isn't a UDF out there since this is definitely how excel saves dates.

Thanks again everyone.

With either Merkie's code or mine I get a 2-day error. Do you get 3/4 from that, or 3/6 in error as I do?

[JasonDW]

With either Merkie's code or mine I get a 2-day error. Do you get 3/4 from that, or 3/6 in error as I do?

You're right...I am sure it worked on my home computer last night...but I could be wrong. I get the same error as you.

If I use 0.0 in excel it is 01/00/1900 0:0:0.... ie it is the zero day, Jan 1900.

If I use 1.0 in excel it is 01/01/1900 0:0:0.

It looks like I should at least subtract 1 day but that doesn't quite get us there either.

It couldn't be directly related to leap years since that doesn't account for only 1 day.

I have to look further into _DateAdd...there is a conversion between Juilian and Gregorian date which I don't understand yet....that might be introducing the one day error.

[JasonDW]

Ok..after some more digging I found the reason for the extra day (since the first extra day was taken care of because the 'start of time' is 1/0/1900 not 1/1/1900).

This extract from a website

"Let me say a little more about how Excel interprets numbers as dates. (Most of the logic for this date scheme originated in the need to be compatible with Lotus 1-2-3 spreadsheets.) The number 1 is interpreted as 1/1/1900. The number 0 is interpreted as 1/0/1900! Excel can deal with dates earlier than 1/1/1900, but stores them as text. So, if your application needs to share those dates, you'll have to parse the date components from a text value and convert them to the format appropriate for the field to which you're appending the data. Also, Excel recognizes 2/29/1900 as a valid date, which it isn't, so that must be accounted for. Versions of Excel prior to Excel 97 only recognized dates through 12/31/2078. From Excel 97 on, dates can go through 12/31/9999."

So it looks like the code needs to be

CODE

#Include <Date.au3>

Local $DatePart, $TimePart

$text = 39876.7707048611

$Date = _DateAdd("D",Floor($text)-2,"1900/01/01 00:00:00")

$Date = _DateAdd("n",Round(($text-Floor($text))*24*60),$Date)

_DateTimeSplit($Date, $DatePart, $TimePart)

$Date = $DatePart[2] & "/" & $DatePart[3] & "/" & $DatePart[1] & " " & $TimePart[1] & ":" & $TimePart[2]

MsgBox(0,"",$Date)

The minus 2 seems obvious BUT I wanted to understand why first.

Anyone have any other ideas?

[Nevin]

This might be useful, or just interesting. Or neither.

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm Article explaining Excel's date format

http://support.microsoft.com/default.aspx?...kb;en-us;214058 - All days of the week before March 1, 1900 are incorrect in Excel

[t0nZ]

Excuse me, but I don't have found anywhere the opposite conversion, from the date to the number, where the number is the same excel (and picasa) date format from 01/01/1900.

Any ideas ?