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