Date Maths

I am trying to calculate time differences between two dates one of which is scraped for a web sit the other from a cell(s) in an Excel Worksheet. Managed to get the Web side working but am stumped with the Excel approach. Running the scrip below I get a message saying the “ExpiryDay” or “ThisDate” is not defined.


set ExpiryDay to current date
set ExpiryDay to short date string of ExpiryDay
set ExpiryDay to date ExpiryDay
tell application "System Events"
	tell application ":Applications:Microsoft Office 2011:microsoft excel.app"
		activate
		set OptDateR to value of cell ("F3")
		date (OptDateR)
		set OptDateR to short date string of OptDateR
		set month of ExpiryDay to word 1 of OptDateR
		set day of ExpiryDay to word 2 of OptDateR
		set year of ExpiryDay to (word 3 of OptDateR)
		set ExpiryDay to short date string of ExpiryDay
		set ExpiryDay to date ExpiryDay
		set ThisDate to short date string of (current date)
		set ThisDate to date ThisDate
		date (ThisDate)
		--#################NOT WORKING message is Expiry and or Thisdate not defined
		display dialog " Date " & ThisDate as string
		set Elapsed to ExpiryDay - ThisDate
		--################
		set DaysElasped to Elapsed / 86400
	end tell
end tel

Your code is too complicated.

First of all don’t wrap an application tell block (Excel) in a System Events tell block. Don’t do that.
Second of all the reference to Excel is simply

tell application "Microsoft Excel"

Third of all to compare dates use dates nothing else. The conversion back and forth to string makes no sense.

If you want to strip the time portion of a date just subtract it.

tell (current date) to set currentDate to it - (its time)

To compare two dates without the time portion use something like


tell (current date) to set currentDate to it - (its time)
tell application "Microsoft Excel"
	set OptDateR to value of cell "F3"
end tell
tell OptDateR to set anotherDate to it - (its time)

if currentDate comes after anotherDate then
	display dialog "date expired"
end if

The English-like AppleScript language provides comprehensible comes before and comes after operands for dates

Stefan

Once again my thanks. I now have it working but I have a question if that is allowed. For some reason I cannot remove the time from the date, it does not really matter as days are OK for what I am trying to do. I also tried converting both dates to the short form, that did not work on the date obtained from the Web. My working script is below.

I have to use the full address for Excel 2011 as I have two versions on my machine .


set sym to "Boom"
tell application "System Events"
	tell process "Safari"
		tell application "Safari" to open location "https://finance.yahoo.com/quote/" & sym & "/options?p=" & sym & ""
		set Y to 9
		repeat until Y = 15
			set Y to Y + 1
			try
				delay 1
				--Select Option Date Pop up (down) Box
				click pop up button 1 of group 2 of group Y of group 1 of UI element 1 of scroll area 1 of group 1 of group 1 of tab group 1 of splitter group 1 of window 1
				delay 1
				key code 36 --Return to accept the date
				delay 1
				--Get the Value of the selected Date
				get value of pop up button 1 of group 2 of group Y of group 1 of UI element 1 of scroll area 1 of group 1 of group 1 of tab group 1 of splitter group 1 of window 1
				set ExpDate to result
				delay 1
				exit repeat
			end try
		end repeat
	end tell
end tell
activate

tell application ":Applications:Microsoft Office 2011:microsoft excel.app"
	activate
	set ExDivDate to value of cell "F3"
end tell
--############ Not removing the time
tell ExDivDate to set ExDivDate to it - (its time)
--########### 
activate
display dialog ExDivDate as string
if ExpDate comes before ExDivDate then
	display dialog ("Option Date " & ExpDate as string) & return & "is After Ex Div Date " & ExDivDate
else
	display dialog ("Option Date " & ExpDate as string) & return & "is Before Ex Div Date " & ExDivDate
	
	tell application "System Events"
		tell process "Safari"
			click pop up button 1 of group 2 of group Y of group 1 of UI element 1 of scroll area 1 of group 1 of group 1 of tab group 1 of splitter group 1 of window 1
			delay 1
			key code 125 --One Step down
			delay 1
			key code 36 --Return to accept the date
			delay 1
			get value of pop up button 1 of group 2 of group Y of group 1 of UI element 1 of scroll area 1 of group 1 of group 1 of tab group 1 of splitter group 1 of window 1
			set ExpDate to result
		end tell
	end tell
	display dialog ("Option Date " & ExpDate as string) & return & "is now after  Ex Div Date " & ExDivDate
end if

Peter

I have already had considerable help on this issue but every time I go to incorporate what I think is the solution it fails to work. I am trying to calculate if one date is before or after another. I have hardcoded the ExDivDate as 9/26/19 and the OptDate as 10/18/19 in the scrip below. The if routine asks “if ExDivDate comes before Optdate then”, the answer I get is the result of the else branch which should mean the ExDivDate was after the OptDate. If I switch the if routine to “if ExDivDate comes after Optdate then”, I get the correct answer. I only learnt about the “before and after” operands form Stefan K the other day. This I know is a really dumb question hopefully some one can point to the error in my logic.


set ExDivDate to "9/26/2019" --When Working this would come from an Excel WorkBook
set ExDivDate to date (ExDivDate) --Make sure its a date
set ExDivDate to short date string of ExDivDate
set Optdate to "10/18/2019" --When Working this will be scraped from a Web Site
set Optdate to date (Optdate) --Make sure its a date
set Optdate to short date string of Optdate

if ExDivDate comes before Optdate then
	display dialog "ExDivDate comes before OptDate" & return & "Ex Div Date " & ExDivDate & return & "Opt Date  " & Optdate
else
	display dialog "ExDivDate comes After OptDate" & return & "Ex Div Date " & ExDivDate & return & "Opt Date  " & Optdate
end if

Thanks

Peter

You’re comparing the strings (short date string of…) you create – you need to compare the dates.

If the affected Excel cells are formatted as date – which I was assuming – the value property returns an AppleScript date.

Thank you very much, with the dates (in the real application) coming form different sources (Excel & the Web) I was trying to make the result easier to read. anyway I have learned something thank you again.