Search code examples
timegoogle-sheetsgoogle-sheets-formulacountingcountif

Google Sheets count If time within time range

I have created a separate sheet to demonstrate what I am after I know the issue lays within the time first being greater than the second time (I.E. 7pm then 4am) but that is how it is needed, Meaning if someone clocks on between those 3rd shift hours they will receive 3rd shift premium. Is there a way I can make a countif for a rolling 24 hours?

This is the code I am using to check clock-in times:

=IF(COUNTIFS(A2,">=7:00 PM",A2,"<=4:45 AM"),"TRUE","FALSE")

My test sheet can be seen HERE, where I have made the cell that should be TRUE noticeable.

My Sheet

Solution

  • try:

    =COUNTIFS(A3, ">=7:00 PM", A3-1, "<=4:45 AM")=1

    0

    UPDATE:

    =ARRAYFORMULA(VLOOKUP(B3:B9, 
 {TIMEVALUE("00:00 AM"), 1; 
  TIMEVALUE("4:45:01 AM"), 2; 
  TIMEVALUE("7:00 PM"), 1}, 2, 1)=1)

    0