Comparing Dates

MitchBVI · March 10, 2021, 12:31am

I want to compare a date from Excel with one from the web. The following script works when the dates are different but not when they are the same.

tell application "Microsoft Excel" to set OptDateExcel to value of cell ("I3") --When Set to 3/19/21
tell application "System Events"
	tell process "Safari" --Value of Button in Yahoo finance March 19,2021
		set OptDateYahoo to value of pop up button 1 of group 2 of group 16 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
	end tell
	set OptDateYahoo to OptDateYahoo as string
	set DateTest to the (current date) - (time of (current date)) as string
	set day of DateTest to word 2 of OptDateYahoo
	set check to OptDateYahoo = OptDateExcel
end tell

When I change “check” to < or = it returns false. If the dates are different it works. I cannot work out how to coerce the date for Yahoo into a date AppleScript recognizes so I know when they are the same. I have tried getting the short date form of the excel date but that of course returns 3/19/2021.

I have also tried parsing the date obtained from Yahoo and then using that to set the details into current date see below. However that fails with the 1st element the day error -10006.

tell application "Microsoft Excel" to set OptDateExcel to value of cell ("I3") --When Set to 3/19/21
tell application "System Events"
	tell process "Safari" --Value of Button in Yahoo finance March 19,2021
		set OptDateYahoo to value of pop up button 1 of group 2 of group 16 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
	end tell
	set OptDateYahoo to OptDateYahoo as string
	set DateTest to the (current date) - (time of (current date)) as string
	set day of DateTest to word 2 of OptDateYahoo
	set check to OptDateYahoo = OptDateExcel
end tell

Any suggestions greatly appreciated.

StefanK · March 10, 2021, 7:43am

My suggestion is to convert the string to date with NSDateFormatter

use AppleScript version "2.5"
use framework "Foundation"
use scripting additions

set dateString to "March 19,2021"
set dateFormatter to current application's NSDateFormatter's alloc()'s init()
set dateFormatter's locale to current application's NSLocale's localeWithLocaleIdentifier:"en_US_POSIX"
set dateFormatter's dateFormat to "MMMM dd,yyyy"
set yahooDate to dateFormatter's dateFromString:dateString

set excelDate to date "Friday, 19 March 2021 at 00:00:00"

set check to excelDate = yahooDate as date

KniazidisR · March 10, 2021, 8:16am

Plain AppleScript solution:


set OptDateYahoo to "March 19,2021"
set OptDateExcel to "3/19/21"

-- Replace month text value with month integer value
set yahooMonth to run script (word 1 of OptDateYahoo & " of {January:1, February:2, March:3, April:4, May:5, June:6, July:7, August:8, September:9, October:10, November:11, December:12}")

-- Create 2 AppleScript date objects
set {yahooDate, excelDate} to {(current date), (current date)}

-- set Yahoo date object
tell yahooDate to set {its month, its day, its year} to ¬
	{yahooMonth, word 2 of OptDateYahoo, text -4 thru -1 of OptDateYahoo}

-- set Excel date object
tell excelDate to set {its month, its day, its year} to ¬
	{word 1 of OptDateExcel, word 2 of OptDateExcel, "20" & word 3 of OptDateExcel}

-- Compare dates
set check to (yahooDate = excelDate) --> true

Yvan_Koenig · March 10, 2021, 3:13pm

Why not using the function short date string to normalize the format and drop the time component ?

set OptDateYahoo to "19 Mars 2021 14:18" --  "March 19,2021"
set OptDateExcel to "19/3/21 15:16" --  "3/19/21"


-- long version

set yahooDate to short date string of date OptDateYahoo
log result --> (*19/03/2021*)
set excelDate to short date string of date OptDateExcel
log result --> (*19/03/2021*)
-- Compare dates
set check to (yahooDate = excelDate) --> true

-- short version

set check to (short date string of date OptDateYahoo = short date string of date OptDateExcel)

As you may see, I tested with french dates.

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) mercredi 10 mars 2021 16:13:02

roosterboy · March 10, 2021, 4:56pm

You can use the TEXT function in Excel to format a date.

So instead of this:


tell application "Microsoft Excel" to set OptDateExcel to value of cell ("I3")

You can do this:


tell application "Microsoft Excel" to set OptDateExcel to evaluate name "=Text(I3,\"mmmm d, yyyy\")"

and it will return March 10, 2021 (or whatever date is in cell I3) and you can compare to the string you get from Yahoo.

MitchBVI · March 11, 2021, 12:18am

Thank you all for taking the trouble to reply. RoosterBoy yours worked and as the shortest my preference. I do have a question though why “mmmm d”. I tried 2 positions for d “dd” and it did not work also tried charging the number of “m”'s. Shows my lack of understanding but do appreciate knowing why something works, thank you again

Yvan for what is worth I couldn’t make your solution work, I think because the date from the button in Yahoo does not have a time component, this is the error message I get
error “Can’t get short date string of "March 19, 2021".” number -1728 from short date string of “March 19, 2021”.

I tried coercing OPtDateYahoo to a date but it would not let me do that. The date on the Button looks like this “March 19,2021”

KniazidisR & Stefank your solutions worked (of course) but more code.

Once again thank you all

Peter

roosterboy · March 11, 2021, 12:43am

Those are the codes for Microsoft’s date format strings. You can find more built-in date formats or create custom ones in the Number tab of the Format Cells dialog.

You can see the available format codes on this page: Number format codes

mmmm is full month name (January, February, etc)
mmm is short month name (Jan, Feb, etc)
d is day number (1-31)
dd is zero-padded day number (01-31)

All of these worked for me:


tell application "Microsoft Excel" to set OptDateExcel to evaluate name "=Text(I3,\"mmmm d, yyyy\")"
-- "March 10, 2021"
tell application "Microsoft Excel" to set OptDateExcel to evaluate name "=Text(I3,\"mmm d, yyyy\")"
-- "Mar 10, 2021
tell application "Microsoft Excel" to set OptDateExcel to evaluate name "=Text(I3,\"mmmm dd, yyyy\")"
-- "March 10, 2021"

MitchBVI · March 11, 2021, 1:05am

Got it and thank you again.

Peter

KniazidisR · March 11, 2021, 4:35am

This method for comparing dates is fundamentally flawed, since you will be comparing as a result strings instead of dates. If you understand what I’m talking about, you should use either my solution or StefanK’s solution. My & StefanK’s solutions doesn’t has local dates problems of other 2 suggestions as well.


date "Thursday, 11 March 2021 - 6:41:08 AM" < date "Sunday, 11 April 2021 - 6:41:08 AM"
--> true (correct result)
date "Thursday, 11 March 2021 - 6:41:08 AM" comes before date "Sunday, 11 April 2021 - 6:41:08 AM"
--> true (correct result)


"Thursday, 11 March 2021 - 6:41:08 AM" < "Sunday, 11 April 2021 - 6:41:08 AM"
--> false (I don't think you need this)
"Thursday, 11 March 2021 - 6:41:08 AM" comes before "Sunday, 11 April 2021 - 6:41:08 AM"
--> false (I don't think you need this)

For equality comparison, the string method will work, and nothing more. Still, it’s best not to use this bad practice, since comparing dates and comparing strings are two different things.

Mockman · March 11, 2021, 9:38am

Try:

short date string of date "March 19, 2021"

You were missing the second ‘date’.

KniazidisR · March 11, 2021, 10:41am

It converts to like Excel’s string, but 1) it is once again string comparing instead of date comparing, 2) the Yahoo date is given to OP in other form then date “March 19, 2021”. And 3) the AppleScript dates has time & weekday components as well. So, the Script Editor is forced to fix your invalid date as well. 4) Your date localization is also tough, so it will give an error in other regions.

Yvan_Koenig · March 11, 2021, 3:32pm

As far as I know, on my system

short date string of date "jeudi 11 mars 2021 à 14:18:00"

would return “11/03/2021”

while

short date string of date "thur, 11 march 2021 - 14:18:00"

will not compile.

This means that:

as far as the passed strings are local valid dates there will be no problem to treat them.
Your proposal will fail if the passed objects aren’t US English valid dates.
if the
as the OP was checking for equality, using short date string is OK. There will be no problem related to spelling of components because objects using no numeric items are dropped.

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) jeudi 11 mars 2021 16:32:21

MitchBVI · March 11, 2021, 7:31pm

Thank you all, an issue in your replies is that you have set the Excel date rather than gathering it from an Excel worksheet. The result when recovered from Excel is “Friday, March 19, 2021 at 12:00:00 AM”

KniazidisR suggested it was bad practice to use strings to compare dates, accepting that I have looked for a solution. I am hoping that what I now have eliminates the problem and actually compares dates and would really appreciate some ideas if its does not.

tell application "Microsoft Excel" to set OptDateExcel to value of cell ("I3") as string
set OptDateExcel to word 2 of OptDateExcel & " " & word 3 of OptDateExcel & "," & " " & word 4 of OptDateExcel
set OptDateExcel to the short date string of date OptDateExcel
tell application "System Events"
    tell process "Safari" --Value of Button in Yahoo finance March 19,2021
        set OptDateYahoo to value of pop up button 1 of group 2 of group 16 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
    end tell
end tell
set OptDateYahoo to the short date string of date OptDateYahoo
log OptDateExcel & " " & OptDateYahoo
log OptDateExcel > OptDateYahoo
log OptDateExcel < OptDateYahoo
log OptDateExcel = OptDateYahoo

the log results
(3/19/21 3/19/21)
(false)
(false)
(true)

Yvan_Koenig · March 11, 2021, 7:55pm

I refuse to host Merdosoft products so I can’t test.
What would return the simple codes:

tell application "Microsoft Excel" to set OptDateExcel to value of cell ("I3") -- return a date value
set OptDateExcel to the short date string of OptDateExcel

tell application "Microsoft Excel" to set OptDateExcel to value of cell ("I3") as string
set OptDateExcel to the short date string of date OptDateExcel

On a machine running US English I assume that both would return : “03/11/2021” but I’m not sure.

CAUTION: If you want to check lower or greater, it would be useful to encapsulate the strings comparison in a considering block.
On a system running in French,

"3/10/2021" > "12/10/2021" --> true

which is not what we are supposed to get.

considering numeric strings
	"3/10/2021" > "12/10/2021" --> false
end considering

which is the wanted behavior.

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) jeudi 11 mars 2021 20:54:39

Yvan_Koenig · March 11, 2021, 8:28pm

Oops, considering numeric strings is not sufficient.

set shortDate1 to "6/12/2021" -- 6 décembre 2021
set shortDate2 to "10/8/2021" -- 10 aout 2021
considering numeric strings
	shortDate1 < shortDate2
	log result --> true which is wrong
end considering
-- Correct comparison
date shortDate1 < date shortDate2 --> false which is correct

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) jeudi 11 mars 2021 21:27:53

MitchBVI · March 11, 2021, 9:08pm

Which is why I used “set OptDateExcel to the short date string of date OptDateExcel --Set result to short date string” so hopefully in my last post I am comparing dates not strings.