Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2011
    Posts
    9
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Red face refining work time

    I am trying to see if an employee has had a mandatory 7 hour break between 22:00 and 8:00. i am having some real difficulties with this. I hope the attached example explains it properly. only yellow cells require user input.
    example time problem.xlsx
    thanks in advance
    Cliff

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am not sure what your calculation is supposed to be doing in B10,Maybe I don't understand completely, but are you looking for something as simple as:
    =IF(B9>7/24,"Yes","No")

    Note:=(C9*24*60)/1440 = C9 since 24*60=1440. To convert to hours just multiply by 24 (excel date/times are stored in days). If you want to display the serialdate as a number (of days) just change the formatting to a non-date type...

    Steve

  3. #3
    New Lounger
    Join Date
    Jun 2011
    Posts
    9
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    I am not sure what your calculation is supposed to be doing in B10,Maybe I don't understand completely, but are you looking for something as simple as:
    =IF(B9>7/24,"Yes","No")

    Note:=(C9*24*60)/1440 = C9 since 24*60=1440. To convert to hours just multiply by 24 (excel date/times are stored in days). If you want to display the serialdate as a number (of days) just change the formatting to a non-date type...

    Steve
    Steve
    I wish it were that simple (perhaps its me that's simple) but no I have already tried that and, yes,it does tell me if someone as had a minimum 7 hour break.
    I need to confirm that the 7 hour break has occurred between 10pm and 8am. If they don't start their break by 1am then they are not complying. eg 8am minus 7 hours is 1am, if they start their break at 1:15 they cannot fit in 7 hours before 8am. this is why I started messing with converting time to serials and so forth.
    Cliff

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Still not sure I follow completely. How about:
    =IF(AND(B9>=7/24, C9<=22/24, B12>=8/24),"Yes","No")

    To get a yes, The elapsed time must be >=7 hours, the end time must be no later than10PM, and the start time must be no earlier than 8 AM...


    Or are you looking for ending no later than 1AM the next day (8AM- 7 hrs = 1AM) and no earlier than 5AM (10PM + 7hrs = 5AM) which would be more like this:

    =IF(AND(B9>=7/24, C9<=25/24, B12>=5/24),"Yes","No")


    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    ozcee (2011-07-07)

  6. #5
    New Lounger
    Join Date
    Jun 2011
    Posts
    9
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Steve
    The first formula seems to be exactly what i am looking for. I think I was just making things too complicated.
    Cliff

Tags for this Thread

Posting Permissions

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