I have been processing Excel Data out side of excel to save time. One thing I do is recover a table from the web post it to an excel worksheet. Then I extract the table and work on it. One of the columns contains a date but its structure also allows for a time function in total there are 29 characters for each date. I trim each entry to the first 10 characters . I then want to use that result to calculate days between that date and todays date. My issue is that the only way I can do that calculation is to post the result back to the Excel worksheet and recover it and it works which of course defeats the objective of working with data outside of excel. In the following script I am only using a couple of cell. A2 has the long date string cell B2 the result which the allows the calculation. I appreciate I could simply set a variable to a date and then replace its elements outside of Excel but I was hoping there may be a way to convert the string .
set TodaysDate to (current date) - (time of (current date))
tell application "Microsoft Excel"
set ExcelString to value of cell "A2"
set ExcelDate to characters 1 thru 10 of ExcelString as string
set the value of cell "B2" to ExcelDate
set ElapsedDays to ((value of cell "B2") - TodaysDate) / 86400
log ElapsedDays
end tell
Check post 16 about creating a NSDate from string, using NSDateFormatter.
Once you calculate the date difference.
You could use their stringFromDate function
Changing the NSDateFormatter dateFormat to what you want to return.
Check the two top answers in this post.
You can either use NSCalenderComponets function.
Or the NSDate’s timeIntervalSinceDate:
Or even timeIntervalSinceNow
-- convert string to date object
set excelString to "2024-03-27T00:00:00.000+00:00"
set eds to text 1 thru 10 of excelString
--> "2024-03-27"
set futureDate to date eds
--> date "Wednesday, March 27, 2024 at 00:00 "
-- get date object for midnight today
set todaysDate to (current date)
--> date "Monday, March 25, 2024 at 14:23 "
set time of todaysDate to 0
todaysDate
--> date "Monday, March 25, 2024 at 00:00 "
set daysApart to (futureDate - todaysDate) / 86400 as integer
--> 2
I tested Mockman’s suggestion, but it returned an incorrect result due to the date format setting on my computer. The following did work, though.
set excelString to "2024-03-27T00:00:00.000+00:00"
set theDate to (current date) -- create date object
set year of theDate to text 1 thru 4 of excelString
set month of theDate to text 6 thru 7 of excelString
set day of theDate to text 9 thru 10 of excelString
set time of theDate to 0
set todaysDate to (current date)
set time of todaysDate to 0
set daysApart to (theDate - todaysDate) / 86400 as integer -- past dates are negative
BTW, excelstring is GMT but for some reason I wonder if setting the time of current date to 0 makes it comparable to GMT.
FWIW, the following script fixes the above issue but only in my locale, which is United States Mountain Time zone with no daylight savings time:
set dateString to "2024-03-27T00:00:00.000+00:00" -- a GMT date
set fromDate to (current date) -- create date object
set year of fromDate to text 1 thru 4 of dateString
set month of fromDate to text 6 thru 7 of dateString
set day of fromDate to text 9 thru 10 of dateString
set time of fromDate to 0 -- midnight
set toDate to (current date) + 7 * hours
set time of toDate to 0
set daysApart to (fromDate - toDate) / 86400 as integer -- past dates are negative
set dateString to "2024-03-27T00:00:00.000+00:00" -- a GMT date
set fromDate to (current date) -- create date object
set year of fromDate to text 1 thru 4 of dateString
set month of fromDate to text 6 thru 7 of dateString
set day of fromDate to text 9 thru 10 of dateString
set time of fromDate to 0 -- midnight
set toDate to (current date) - (time to GMT)
set time of toDate to 0
set daysApart to (fromDate - toDate) / 86400 as integer -- past dates are negative
FWIW, the following is an ASObjC solution:
use framework "Foundation"
use scripting additions
set dateFormatter to current application's NSDateFormatter's new()
dateFormatter's setDateFormat:"yyyyMMdd"
dateFormatter's setTimeZone:(current application's NSTimeZone's timeZoneWithAbbreviation:"GMT")
set dateString to "2024-03-27T00:00:00.000+00:00"
set dateString to text 1 thru 4 of dateString & text 6 thru 7 of dateString & text 9 thru 10 of dateString
set fromDate to dateFormatter's dateFromString:dateString
set toDate to current application's NSDate's now()
set toDate to dateFormatter's stringFromDate:toDate
set toDate to dateFormatter's dateFromString:toDate
set dateDifference to ((fromDate's timeIntervalSinceDate:toDate) as integer) div 86400 -- past dates are negative
Both of the above solutions returned accurate results over several days of testing. The timing results for the AppleScript and ASObjC scripts were 4 and 1 milliseconds, respectively.
If you have a column of date strings you want to work with, you can use something like this to make Excel do all the work for you:
--assuming you have a list of date strings in A1:A20
tell application "Microsoft Excel"
tell active workbook
tell active sheet
set ddd to evaluate name ¬
"=DATEDIF(NOW(),DATEVALUE(LEFT(A1:A20,10)),\"d\")"
end tell
end tell
end tell