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


Let's say the start times are in D2 and down.

In E2 or another cell in row 2, enter the formula




or if you prefer




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.