I’m working with an export from a proprietary calendaring system. I export the file, reformat it, and then import it into Entourage. One issue I’m encountering is that the export only lists the start date of the calendar event. If, for instance, the event starts at 10PM on one day and ends at 2AM on the next, the end date does not show up as the next day. It’s on the same day. And entourage doesn’t like seeing an end time that comes before the start time.
So, my question is this: How can I compare the start time and the end time to determine which comes first?
If I determine that the end time is before the start time, I can just take the start date and add one day to it.
The start time and the end time come in string format as “hh:mm AM” or “hh:mm PM”.
Any ideas?
David Wilcox
Model: Powerbook G4 Aluminum
AppleScript: Applescript 1.10.3
Browser: Firefox 1.0
Operating System: Mac OS X (10.4)
Without seeing the exported file (or at least a portion of it) I can only guess at a solution:
-- assumes this data comes from the exported file
set SD1 to "2:00 AM"
set ED1 to "10:00 PM"
set SD2 to "10:00 PM"
set ED2 to "02:00 AM"
-- possibility #1: event starts and ends on same day
set sDate to date SD1
set eDate to date ED1
set dDate to eDate - sDate
--> result: 72000
-- possibility #2: event starts and ends on different days
set sDate to date SD2
set eDate to date ED2
set dDate to eDate - sDate
--> result: -72000
SD1, SD2, ED1, ED2 will come from the exported file (I set them here to illustrate the possible solution). If the result is a positive number then the start and end of the event are on the same day. If the result is negative then the start and end of the event are on different days. [i]This will only work if the end time is less then the start time.[/i] If the event starts on day 1 at 10:00 AM and end on day 2 at 11:00 AM, this won’t work.
If you can post a day’s event perhaps a better solution can be found.
‘days’ is an AppleScirpt constant for the number of seconds in a day. You could try replacing it with 86400 or do the date calculation outside the Excel tell block. Excel doesn’t know what ‘days’ means or there is conflict with an Excel keyword ‘days’. Usually there’s a conflict, because if the target (Excel) doesn’t know what the keyword means then the script looks to the next target (in this case the script).
For some reason, Excel can’t coerce the string “November 01, 2006” into a date directly. Strangely, it can read one of it’s own cells back in as a date type.
Here’s the somewhat ugly result…
tell application "Microsoft Excel"
set theLongDate to "November 01, 2006"
set value of cell 1 of column 3 to date theLongDate
set theLongDate to (((value of cell 1 of column 3) as date) + 86400)
set value of cell 1 of column 3 to date theLongDate
end tell
--result "Thursday, November 2, 2006 12:00AM"
It works, but it’s uuuuuugllyyyyyy. Any other suggestions, for the sake of being efficient?