I’m trying to convert H:M:S times into fractions of the day. e.g. 6AM = .25 in a list.
I have to test for AM or PM and add 0.5 if the time is between 1 PM and 12 midnight.
The code is calculating the time correctly but prior to 1 PM it is putting the value into an item within an item in the list I am creating. After 1 PM it is putting the value into an item in the list. The code is simple but I can’t get the data in the list to be consistent. Can anyone tell me what is going on?
Results:
NewTime (the list)
Item 1 list of 1 item (value is 0.53479)
Item 2 list of 1 item
…
Item 5 0.54398
Item 6 0.54685
…
Item 9 0.5577
beep 2
tell application "Finder"
set ScanFile to (choose file with prompt "Select Scanner .txt file to convert for analysis:" without invisibles)
end tell
tell application "Microsoft Excel"
activate
open text file filename (ScanFile as text) origin MSWindows ¬
start row 2 ¬
data type delimited ¬
with space and tab
tell used range of active sheet to set {maxRows, maxColumns} to {count rows, count columns}
set SC_rows to maxRows
set SC_cols to maxColumns
set ScanTimes to the value of range ("B1:B" & (SC_rows))
set AMPM to the value of range ("c1:c" & (SC_rows))
set NewTime to {}
repeat with p from 1 to SC_rows
set AP to ((item p of AMPM) as string)
set TIM to (item p of ScanTimes)
if AP = "AM" then
set end of NewTime to TIM
else
if TIM ≥ 0.5 then
set end of NewTime to TIM
else
set end of NewTime to TIM + 0.5
end if
end if
end repeat
end tell
/applescript]
Thanks
Dick
Partly because I don’t use or understand excel I don’t understand your script but I can solve “I’m trying to convert H:M:S times into fractions of the day. e.g. 6AM = .25 in a list.” without glitches concerning 1 am.
set the theDate to characters of "4/6/10 12:50:06 PM"
set the theTime to items -14 thru -7 of theDate -- adjust these to match date string if they are not what you have posted
set varperiod to "p"
if items 1 thru 2 of theTime is {"1", "2"} then
set item 1 of theTime to "0"
set item 2 of theTime to "0"
set varperiod to "a"
end if
log theTime
if item -2 of theDate is varperiod then
set valreplace to characters of (((items 1 thru 2 of theTime as string as integer) + 12) as string)
set item 1 of theTime to item 1 of valreplace
set item 2 of theTime to item 2 of valreplace
end if
set theSeconds to time of date (theTime as string)
return theSeconds / 86400
I noticed he was using 12 as an hour on a 12 hour clock, where I come from it would be 0, I assumed 12 PM = 0 AM because 1 hour after 11 PM is 0 AM. Considering that it should work.
I like how you used multiplication of booleans to make it so small.
Corrected (can’t remember my last debug where I deleted code ):
set the theDate to "4/6/10 12:50:06 PM"
set the theTime to items -14 thru -7 of theDate -- adjust these to match date string if they are not what you have posted
if items 1 thru 2 of theTime is {"1", "2"} then
set item 1 of theTime to "0"
set item 2 of theTime to "0"
end if
log theTime
if item -2 of theDate is "p" then
set valreplace to characters of (((items 1 thru 2 of theTime as string as integer) + 12) as string)
set item 1 of theTime to item 1 of valreplace
set item 2 of theTime to item 2 of valreplace
end if
set theSeconds to time of date (theTime as string)
return theSeconds / 86400
Thanks Nigel. A very elegant solution but I need some more help.
The problem I have now is getting the data in the form you are using in the script. The raw data is in a .txt file which I am trying to open in Excel. The form of the text file is as follows:
For the life of me I haven’t been able to set up the parameters for opening a text file in Excel to end up with the dates and times; 4/6/2010 8:20:57 AM in one column and the other data; S00497 in another column.
I’ve tried using text qualifier single quote as part of the script but it I can’t get it to compile
open text file filename (ScanFile as text) origin MSWindows ¬
start row 2¬
data type delimited ¬
with text qualifier single quote
Hi, Dick. I’ve just seen your latest post while logging on to post this, which concerns your original problem:
The script in your original post only makes sense if the data extracted from column B of the spreadsheet are already fractions of a 12-hour day, so that’s not the point of the exercise here. The goal is to create 24-hour-day fractions from 12-hour-day ones.
Like Richard, I’m not familiar with scripting Excel; but from the symptoms and from a vague recollection of a post I read the other day, it’s possible that the column data are being returned as a list of single-item lists anyway. It’s the addition of the .5 to the later times which makes them come out right:
-- This is possible because AppleScript automatically coerces the single-item list to the item itself before doing the arithmetic.
{0.043981481481} + 0.5 --> 0.543981481481
You probably just need to coerce TIM to a number when you get it, as you do AP to a string in the line above.
repeat with p from 1 to SC_rows
set AP to ((item p of AMPM) as string)
set TIM to (item p of ScanTimes) as number -- NB.
if AP = "AM" then
set end of NewTime to TIM
else
if TIM ≥ 0.5 then
set end of NewTime to TIM
else
set end of NewTime to TIM + 0.5
end if
end if
end repeat
Your new approach might look something like this:
set NewTime to {}
set theRows to paragraphs of (read myTextFile as string)
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to "#"
repeat with thisRow in theRows
tell text item 2 of thisRow to set end of NewTime to (((word 4) mod 12 + 12 * ((word -1 is "PM") as integer)) * hours + (word 5) * minutes + (word 6)) / days
end repeat
set AppleScript's text item delimiters to astid
NewTime
):