TODAY in a days-since column that everything else references. Replaced it with a single cell holding TODAY and pointed the column at that cell. From 18 seconds to under two.
Leo
@listcomp_leo
Teaches an evening class and has watched two hundred people meet mutable default arguments the hard way.
Ctrl+end took me to row 26,914. I have no memory of ever putting anything down there.
QUERY assigns one data type per column, and it decides by looking at the majority of the first chunk of rows. Whichever type loses gets returned as blank, not converted. That is why sorting flips which half disappears — you changed which type is in the majority up top.
The pragmatic fix is a helper column that forces everything to text: =ARRAYFORMULA(D2:D&"") in a spare column, then query that column instead of D. Ugly, reliable, done in thirty seconds.
There is also a header row trick — passing 0 as the headers argument — which helps when the issue is a misread header, but with genuinely mixed data in one column it will not save you.
Good call, and I reach for FILTER first these days for exactly this reason. QUERY earns its keep on group by and pivot, not on plain where clauses.
Handy debugging habit: pull each piece out into its own cell as a standalone array first. =ARRAYFORMULA(D2:D > E2) on its own shows you immediately that it produces one value rather than a column. Testing the pieces separately finds these in a minute instead of an afternoon.
If you can change the import at all, that is the cheaper fix. In the CSV import dialog turn off convert text to numbers and dates, so nothing gets guessed, then convert deliberately with the formula above. Half the pain here is Sheets being helpful at the wrong moment.
Two practical warnings on the IMPORTRANGE route. Each source needs authorising once, and a single broken one shows as one #REF that takes down the whole stack, so leave a note about which row range belongs to which site. And IMPORTRANGE refreshes on its own schedule, so numbers can be a few minutes stale — fine for a tracker, not fine if someone is reading it live on a call.
Add the site name as a column inside each source tab rather than trying to add it during the stack. Much easier to read later and it survives reordering.