Apr 10 2022 08:57 AM - edited Apr 10 2022 08:58 AM
Hello, I have a workbook which has a column that gives the start time of a duty shift. I want to create a formula for another column which checks if the start time of the duty shift falls between 10pm and 8am (a two day span), and then provide a YES/NO answer in the new column. Does anyone have any ideas, please? :)
Apr 10 2022 09:01 AM
Let's say the start times are in D2 and down.
In E2 or another cell in row 2, enter the formula
=OR(E2<TIME(8,0,0),E2>=TIME(22,0,0))
or if you prefer
=IF(OR(E2<TIME(8,0,0),E2>=TIME(22,0,0)),"Yes","No")
Fill down.
Apr 10 2022 09:12 AM
Apr 10 2022 09:22 AM
@CymruInThe70s As a variant:
Apr 10 2022 09:58 AM - edited Apr 10 2022 10:16 AM
Hmmm...those don't seem to work.
So, maybe I should describe in more detail. I have in 'Column A' a series of start times in the format '12:04:00', in rows 1-27. I need a new column to tell me if any start time falls between 22:00:00-08:00:00.
In column B (or somewhere else), I'd like to create a cell which produces a YES/NO response if the value in Column A falls between 22:00:00 and 08:00:00.
Apr 10 2022 10:06 AM
Apr 10 2022 10:16 AM
@CymruInThe70s Attaching the file I used in my example.