'Find the earliest and latest time for each day based on a time range criteria in Google Sheets
I have two columns in Google Sheets:
B | C Date | Time 2/21/2022 | 7:30:02 AM 2/21/2022 | 7:48:02 AM 2/21/2022 | 8:24:05 AM 2/21/2022 | 2:34:02 PM 2/21/2022 | 2:56:02 PM 2/22/2022 | 8:05:00 AM 2/22/2022 | 8:15:00 AM 2/22/2022 | 3:00:02 PM 2/22/2022 | 3:15:02 PM 2/22/2022 | 3:25:14 PM etc...
There are dozens of entries for Time in a single day. I would like to find the earliest Time and latest Time for each day within a Time range between (7:30:00 AM to 8:30:00 AM) AND (2:30:00 PM to 3:30:00 PM). The result will be two Time values for each day. i.e.
B | C Date | Time 2/21/2022 | 7:48:02 AM 2/21/2022 | 2:56:02 PM 2/22/2022 | 8:05:00 AM 2/22/2022 | 3:15:02 PM etc..
I was able to find the values of each day within the Time Range using this formula, but have not been able to extract the Lowest and Highest Time values for each day.
=ArrayFormula((IF(C2:C="","",IF((C2:C1000>VALUE(timevalue("7:30 AM")))(C2:C1000<VALUE(timevalue("8:30 AM"))),C2:C1000,IF(C2:C="","",IF((C2:C1000>VALUE(timevalue("2:30 PM")))(C2:C1000<VALUE(timevalue("3:30 PM"))),C2:C1000,0))))))
Any ideas? Thanks!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
