2

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.

2

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 ! – Jason Jan 18 '16 at 4:11
  • 1
    Glad 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... – user79865 Jan 18 '16 at 4:13
0
  • 3 PM is 15h so you can do:

    =FILTER(A1:D, HOUR(C1:C) >= 15)

    0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.