How do I see if the time worked (start and finish times) is in a span of defined hours.

For instance how do I check that hours worked or part hours worked are within or not within the range 18:00 to 00:00 on day 1 and 00:00 to 06:00 on day two?

I have used the formula below where F5 is the start, F6 finish times for lunch, F8 start time after lunch F9 Finish time and F11 & F12 start and finish times.

start_1 is defined as 18:00, Finish_1 as 00:00 for day one, Start_2 as 00:00 and Finish_2 as 06:00 for day two

=IF(OR(AND(F5>=Start_1,F5<=Finish_1,F5<>""),AND(F5>=Start_2,F5<=Finish_2,F5<>""),AND(F6>=Start_1,F6<=Finish_1,F6<>""),AND(F6>=Start_2,F6<=Finish_2,F6<>""),AND(F8>=Start_1,F8<=Finish_1,F8<>""),AND(F8>=Start_2,F8<=Finish_2,F8<>""),AND(F9>=Start_1,F9<=Finish_1,F9<>""),AND(F9>=Start_2,F9<=Finish_2,F9<>""),AND(F11>=Start_1,F11<=Finish_1,F11<>""),AND(F11>=Start_2,F11<=Finish_2,F11<>""),AND(F12>=Start_1,F12<=Finish_1,F12<>""),AND(F12>=Start_2,F12<=Finish_2,F12<>"")),F15,"")

The problem is that that formula is very cumbersome and not good programming and I would have to train useers to use 24:00 instead of 00:00 but then you canot use subtraction to get the times of hours worked; 03:00 minius 24:00 does not work where as 03:00 minus 00:00 = 3 hours

## Bookmarks