Time Logic and Date Math

Scripting Forums AppleScript | Mac OS X
,
#1

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)

#2

David,

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.

Hope this helps,
Brad Bumgarner, CTA

#3

Hi,

Or you can just compare the dates:

if eDate < sDate then set eDate to eDate + 1*days

gl,

#4

Nice catch Kel. That is simplier than what I originally posted. However the same disclaimer (as I posted in my first response) still applies.

Brad Bumgarner, CTA

#5

Brad’s disclaimer is important and limiting enough that I’ve decided to approach this from a different direction. But, now, I have a new question…

The export breaks the date into several pieces. After doing some manipulating, what I end up with is a string like “November 01, 2006” for the date.

This works…


tell application "Microsoft Excel"
set theLongDate to "November 01, 2006"
set value of cell 1 of column 3 to date theLongDate
end tell

but this doesn’t work…


tell application "Microsoft Excel"
set theLongDate to "November 01, 2006"
set value of cell 1 of column 3 to (date theLongDate + (1  * days))
end tell

I get the error "Can’t make «constant e141

Model: Powerbook G4 Aluminum
AppleScript: Applescript 1.10.3
Browser: Firefox 1.0
Operating System: Mac OS X (10.4)

#6

For some reason, that last part got cut off.

I get the error “Can’t make <> into type number.”

Any ideas?

David Wilcox

Model: Powerbook G4 Aluminum
AppleScript: Applescript 1.10.3
Browser: Firefox 1.0
Operating System: Mac OS X (10.4)

#7

Hi,

‘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).

gl,

#8

Just figured it out.

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?

David Wilcox

#9

Try something like this, David:

tell application "Microsoft Excel"
	set theLongDate to "November 01, 2006"
	set value of cell 1 of column 3 to (my (date theLongDate)) + 86400
end tell