I am responsible for determining staffing levels for a Contact Centre and have used threads on ‘returning a value if the time falls between two intervals to develop a spreadsheet to show at any point in the day the number of people at work.

I also wanted to colour code people by the shift they are working. So I have used conditional formatting with the Time function to achieve this, but have this problem with the over midnight issue.

I want to create a formula similar to that below (which does not work) for a shift that starts at 22:00 and ends at 06:00 the following day.

=AND(=TIME>=(22,0,0),=TIME(<=06,0,0))

Any help would be appreciated.