Create a formula which checks if a shift start time falls within the period 10pm to 8am

New Contributor

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?

6 Replies

@CymruInThe70s 

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.

Thank you so much for those, I'll try them. Thank you for your quick response, it's much appreciated.

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.

@CymruInThe70s Attaching the file I used in my example.