Hi,
I have set out to filter rows based on start & end dates, paste the rows ideally in a different worksheet. Dates are in column 1 (usually) then 3-4 columns with associated data
Issues
Unable to pass date to excel function
Output → cant get all the columns & rows
In excel when FILTER formula is entered in cell A30, with 5 filtered rows, and 4 columns, it would fill the range A30:D35, but with me it does not
here is my try, please suggest
(*
FILTER Function
Return value → Array of filtered values
Syntax → =FILTER (array, include, [if_empty])
Arguments
array - Range or array to filter.
include - Boolean array, supplied as criteria.
if_empty - [optional] Value to return when no results are returned.
Version → Excel 365 Only
*)
use framework "Foundation"
use scripting additions
set mySheetArray to {}
tell application "Microsoft Excel"
set enteredText to the text returned of (display dialog "Enter START date, End Date and Column number with Dates (number), separated by comma and a space :" default answer "6/4/20, 5/4/21, 1")
set {ATID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, ", "}
set startDate to my stringToDate(text item 1 of enteredText)
set endDate to my stringToDate(text item 2 of enteredText)
set colNo to (text item 3 of enteredText) as integer
set AppleScript's text item delimiters to ATID
tell active sheet
set mySheetArray to used range
set name of mySheetArray to "theArray"
set col1 to column colNo of used range
set name of col1 to "DateCol"
-- Filter Formula
-- this works with startDate in A16, and endDate in A17
-- set filteredList to (evaluate name "=FILTER(theArray, (DateCol<=A17)*(DateCol>=A16))")
-- this does not work
set filteredList to (evaluate name "=FILTER(theArray, (DateCol<=endDate)*(DateCol>=startDate))")
-- Output
set the clipboard to filteredList -- Script shows output, but nothing to paste in excel
--set formula of cell "A30" to (evaluate name "=FILTER(theArray, (DateCol<=A17)*(DateCol>=A16))") -- gets correct value in cellA30, but does not expand to A30:D35
end tell
end tell
on stringToDate(thisString)
tell current application
-- ** finds all the matches for a date, the result is a NSArray **
set m to its ((NSDataDetector's dataDetectorWithTypes:(its NSTextCheckingTypeDate) |error|:(missing value))'s matchesInString:thisString options:0 range:{0, length of thisString})
if (count m) > 0 then
set d to (item 1 of m)'s |date|() -- get the NSDate of the first item
set df to its NSDateFormatter's new() -- create a NSDateFormatter
df's setDateFormat:"dd/MM/yyyy" -- a specified output format: "day/month/year" (day and month = two digits, year = 4 digits)
return (df's stringFromDate:d) as text
end if
end tell
return "" -- no match in this string
end stringToDate
My version of excel (2011) doesn’t offer the filter function so I can’t run your entire script, however, it could be that the filter isn’t getting the dates as dates. Rather, it is just getting ugly text that doesn’t sort or filter naturally (perhaps if the dates were yyyy-mm-dd then they might filter… dunno).
This would also explain why the filtering works when you enter the dates into cells A16:17.
Maybe add an intervening step to convert the entered text into excel date objects.
Dates A16 & A17 are dd/mm/yyyy format, and the handler “on stringToDate(thisString)” converts them into dd/mm/yyyy format, what are your thoughts on passing dates to excel with user input
Also, would you suggest any other excel function to filter based on dates. I used FILTER as it was easiest to do
The point (and problem and you have) is that they’re still text strings. There are probably several approaches that you could take.
What happens if you get the 5-digit date code (i.e. based on the number of days since some day in 1900). Using the example of April 14, 2020, the result of =DATE(2020,04,14) would be 43935. If you enter a date in excel and then format its cell with ‘general’, then you will see the date code. When you see ‘14/04/20’, it’s the same value but it is formatted differently.
Of course, a set of such numbers will sort and filter normally. So maybe try converting the two entered dates to such numbers.
I’d begin by just playing around in the spreadsheet, using the filter function, and seeing what you can work with. One example would be to use =DATEVALUE(“2020/04/14”) which should also result in 43935 — note that it takes a string (like what your dialogue returns) and returns a date object. That might be the way to go. If you look =DATEVALUE up in excel’s help, it should provide some syntax examples.
Hi
It has been a struggle
I have tried & failed to pass date as text into =DATEVALUE function
tell application "Microsoft Excel"
set startDate to the text returned of (display dialog "Enter START date" default answer "12/10/21")
evaluate name "=DATEVALUE(startDate)"
----> returns missing value
evaluate name "=DATEVALUE(\"12/10/21\")"
----> returns 4.4481E+4
end tell
My difficulty is passing a variable “startDate” into excel function, and for that matter in any excel Function. is there a trick to do that ?
Cheers
Part of the problem is that in something like “=DATEVALUE(startDate)” you aren’t passing a date into the Excel function but instead you are giving it the string “startDate”.
startDate is an Applescript variable and you need to pass its value into Excel.
So you need to take your startDate and endDate variables in you script and plug their values into the formula instead of their names. Something like this:
set fml to "=FILTER(theArray,(DateCol<=DATEVALUE(\"" & endDate & "\"))*(DateCol>=DATEVALUE(\"" & startDate & "\")))"
You actually have it. NB my date order is different so I have edited it.
tell application "Microsoft Excel"
-- date: October 12, 2021
set sd to the text returned of (display dialog "Enter START date " default answer "21/10/12")
set xy to "=DATEVALUE(\"" & sd & "\")" -- note the escaped quotes around the date string
set dateCode to (evaluate name xy) as integer
--> 44481
-- or in a single line…
set xy to (evaluate name "=DATEVALUE(\"21/10/12\")") as integer
--> 44481
set value of range "A16" to xy
end tell
