New Contributor

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

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

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

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.

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

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

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

@CymruInThe70s As a variant:

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

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.

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

OK, Riny, I'll try that. Thanks.

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

@CymruInThe70s Attaching the file I used in my example.