# How to check times fall within(or not within) a span of hours?

1. ## How to check times fall within(or not within) a span of hours?

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

2. While I don't have an answer for you , perhaps one of Chip Pearson's ideas will help. Check his website at: Chip Pearson's site. Look under "D" for "Date" topics.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1