To convert YYYY, MM, DD to an actual Date object, you need the DATE
command. The following works:
=filter(A6:F371,A6:A371>=DATE(2015,1,1),A6:A371<=DATE(2015,1,31))
But I see you want to filter based on dropdown entry. For this I would use MONTH
function. For example, suppose cell J10 has validation rule: list of items
01 (January), 02 (February), 03 (March), and so on
Then your filter in J11 could be
=filter(A6:F371,MONTH(A6:A371)=VALUE(LEFT(J10,2)))
The idea here is that the names of month help the user in selection, but the filter uses the numeric format (month number).
Cells containing the empty string ""
appear empty but are not (isblank
returns FALSE for them). They get sorted ahead of nonempty strings, which is not what you want. To avoid this, replace
if(isblank(B1:B), "", datevalue(B1:B))
by the simpler
iferror(datevalue(B1:B))
The command iferror
returns its (optional) second argument if there is an error evaluating the first argument; otherwise it leaves the cell blank. Blank cells are sorted to the bottom.
A potential drawback of the above is that misformatted dates are simply ignored, while you may want to see and fix them. This is something you can address separately; e.g., have a column with
if(isblank(B1:B), 0, if(iserror(datevalue(B1:B), 1, 0))
and sum over it to get the total number of nonempty but invalid date cells.
Another potential issue is that you are sorting a range containing arrayformula
. The sort may move the formula to another row, creating a mess. It's safer to exclude the header row from sorting. Replacing
var data = JobList.getDataRange();
by
var data = JobList.getDataRange().offset(1,0);
would do it.
Best Answer
The issue is with protected ranges (in the toolbar under View > Protected Ranges). One of the columns was considered a protected range, and so any sorting was considered a temporary filter. Removing the protected status solved the issue.
Source: Works with OP.