Forum Discussion
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
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.
