1. ## 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
Cliff

2. 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. Originally Posted by sdckapr
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

=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. Steve
The first formula seems to be exactly what i am looking for. I think I was just making things too complicated.
Cliff