I've started using Google Sheets for our logbook and it's been a great help. I have data with a time component in it (start and end time) and I'd like to query or filter the data where the start time is past 3:00 pm. I tried looking it up but most answers are with regards to the date. Any help, please? Column C contains the start time and Column D contains the End time.
-
Found one working with importrange and cell reference here: webapps.stackexchange.com/questions/124809/… With Demo here: i.imgur.com/N82AZG0.gif– LodCommented Jul 23, 2023 at 13:01
Add a comment
|
3 Answers
The query would be like this:
=query(A:D, "select * where C > timeofday '15:00:00' ")
(or >= if you want to include 3 PM sharp)
Here, the single-quoted string 15:00:00 is preceded by the keyword timeofday, indicating its meaning. This is similar to how date strings are preceded by date. Note that the format of query string is fairly rigid: it has to be 24-hour time, with the seconds field included (HH:mm:ss). However, the time data in your spreadsheet can be in whatever format you want.
See scalar functions for more examples of manipulation with timeofday values.
-
Hi ! Thanks for the reply ! Great help ! Just did your suggestion on my worksheet. I'll have a look at the scalar functions. Thanks again !– JasonCommented Jan 18, 2016 at 4:11
-
2Glad it works. On Stack Exchange, one can mark an answer as accepted by clicking the checkmark to the left ot it; this is our way of saying "thanks" without posting...– user79865Commented Jan 18, 2016 at 4:13
3 PM is
15hso you can do:=FILTER(A1:D, HOUR(C1:C) >= 15)
If you don't want to use any formula you can use filter view by clicking filter view > create new filter views. The filter view icon can be found at the right top corner before the sigma symbol. After that, click the icon on the cell title (make sure the cells have title). It will show a dropdown menu and choose filter by condition. Choose any option available for numbers because, hours can be treated like numbers in google sheet. 
