Jump to content
Sign in to follow this  
JasonDW

Time Conversion

Recommended Posts

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 by JasonLA

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
Authenticity

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by JasonLA

Share this post


Link to post
Share on other sites
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.

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
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 by Authenticity

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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?

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
JasonDW

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

a) the full number was actually 39876.7707048611

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

Share this post


Link to post
Share on other sites
PsaltyDS

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

a) the full number was actually 39876.7707048611

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

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
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?

:unsure:

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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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 ?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×