+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    02-15-2005
    Posts
    6

    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. #2
    Registered User
    Join Date
    09-10-2003
    Location
    Detroit, MI
    Posts
    59
    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.
    Neopolitan (Florida Dreaming)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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