Conditional Formating Time FunctionOver Midnight

[AlexRoberts]

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.

[teylyn]

Hello,

you need to understand that comparing a time like 22:00 with 06:00 of the next day, you will need to add one day to the second time value and treat the data as a date with time, not just time, otherwise, the second time value will always be smaller than the first.

This may be easier to solve if you posted a sample file with your data structure and some sample data, outlining the expected results manually.