Sorting Rows By Date Using Query in Google Sheets

1
votes

This query function is pulling the correct data, but for some reason its still not sorting the rows by date - any suggestions?

Copy of sheet: https://docs.google.com/spreadsheets/d/10LTLrb66F8t3jOPrvtuDs8wCrsSmNPcP953O_IWpjbM/edit?usp=sharing

=QUERY('Zapier Leads'!A2:AA, "SELECT A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, Z, AA, T, U, V, W, X, Y WHERE not A Matches 'selection8_address' order by 6 asc", 1)

enter image description here

3
sortinggoogle-sheetsformattinggoogle-sheets-formulagoogle-sheets-query
Kristan, to get the best response, please share a copy of your sheet - editable by all. This lets us see exactly what you have, and how your data is structured. I'm sure we can give you the answer you need, but using queries with dates can sometimes be tricky. - kirkg13
Just added the link to my sheet above to the original question - let me know your suggestions. Thanks again! - Kristan Servidad

3 Answers

1
votes

You mentioned

...not sorting the rows by date

The issue is that in your query formula at the Master tab you use =QUERY('Zapier Leads'!A2:AA, "....",1)
This means that you consider the first row as a header.
As a result cell F2 is NOT formatted as Date.

Please use the following formula where the issue is fixed.

=QUERY('Zapier Leads'!A2:AA, "SELECT A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, Z, AA, T, U, V, W, X, Y WHERE A is not null and not A Matches 'selection8_address' order by F")

In addition.

For your Last7DaysLeads tab you can use this simplified formula.

=QUERY(QUERY(Filtered!A1:Z,"", 1), "WHERE Col24 > 10000 AND Col25 > 10000 order by Col6",1)

Notice how we use order by F in the first formula, while order by Col6 in the second.

You can check your sheet to see the formulas in action.

0
votes

I think it is because you are sorting by 6 instead of the date you want to.

Try here where you have order by 6 asc change it to where the column of the dates are: order by (dateColumnNameHere) asc or order by 6, (dateColumnNameHere) asc.

Reference:

0
votes

Having a copy of your sheet would make this easier, but from the statement in your question, you probably want the following.

=QUERY('Zapier Leads'!A2:AA, "SELECT A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, Z, AA, T, U, V, W, X, Y WHERE not A Matches 'selection8_address' order by F asc", 1)

Note the change of your order by 6 to order by F, to indicate to sort by column F, assuming that is where your dates are.

However if the data in column F is not all in date format, you can get unexpected results.

Please let us know if this has helped, or if you still have an issue.