Ms Excel Date & array

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

  • 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])
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.

Hi Mockman

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

Thank you

Hi One,

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.

It has been a struggle :frowning:
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 ?

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.

This formula does what you want in 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 & "\")))"

Give that a try.

You might wish to review this post: :smiley:

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