JasonDW Posted March 5, 2009 Share Posted March 5, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
weaponx Posted March 5, 2009 Share Posted March 5, 2009 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? Link to comment Share on other sites More sharing options...
Authenticity Posted March 5, 2009 Share Posted March 5, 2009 More interestingly is how old is the date it's according to?? like 1\1\1970? or 1\1\1700? Link to comment Share on other sites More sharing options...
Merckie Posted March 5, 2009 Share Posted March 5, 2009 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. Link to comment Share on other sites More sharing options...
JasonDW Posted March 5, 2009 Author Share Posted March 5, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 5, 2009 Share Posted March 5, 2009 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. 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 Link to comment Share on other sites More sharing options...
Authenticity Posted March 5, 2009 Share Posted March 5, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
Merckie Posted March 5, 2009 Share Posted March 5, 2009 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) Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 5, 2009 Share Posted March 5, 2009 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? 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 Link to comment Share on other sites More sharing options...
JasonDW Posted March 5, 2009 Author Share Posted March 5, 2009 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. Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 5, 2009 Share Posted March 5, 2009 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 AMThe 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? 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 Link to comment Share on other sites More sharing options...
JasonDW Posted March 5, 2009 Author Share Posted March 5, 2009 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. Link to comment Share on other sites More sharing options...
JasonDW Posted March 5, 2009 Author Share Posted March 5, 2009 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 beCODE#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? Link to comment Share on other sites More sharing options...
Nevin Posted March 5, 2009 Share Posted March 5, 2009 This might be useful, or just interesting. Or neither.http://www.ozgrid.com/Excel/ExcelDateandTimes.htm Article explaining Excel's date formathttp://support.microsoft.com/default.aspx?...kb;en-us;214058 - All days of the week before March 1, 1900 are incorrect in Excel Link to comment Share on other sites More sharing options...
t0nZ Posted October 15, 2010 Share Posted October 15, 2010 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 ? Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now