# Work Schedule with lunch breaks if over 8hrs

#### jparks1015

##### New Member
I have a worksheet set up as a schedule for 8 people, for the entire month.

Z6 & Y6 have the start time & end time of this persons shift on a specific date (if they are not working it's left blank).
If there are work times in the cells, I want it to subtract the end time from the start time (which usually equals 8.5hours).
I then want it to subtract the lunch break.
AB5 & AA5 are the lunch in and lunch out times for all workers.

My current formula is working and is pasted below.

=IF(AND(ISNUMBER(FEBRUARY!Z6),ISNUMBER(FEBRUARY!Y6)),(FEBRUARY!Z6-FEBRUARY!Y6)-(FEBRUARY!\$AB\$5-FEBRUARY!\$AA\$5),"")

What I want to do now is, have the formula calculate Z6-Y6 and... IF the answer is more than 8 hours, subtract the lunch break. If it is less than 8 hours, I don't want it to subtract the lunch break.

Is this possible? If so, can you show me how to add this variable to the current formula?

Last edited:

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### mrshl9898

##### Well-known Member
How's this?

The check for 8 hours or more is >=0.33.

=IF(IF(ISNUMBER(Y6),Z6-Y6,"")>=0.33,IF(ISNUMBER(Y6),(Z6-Y6)+(AB5-AA5),""),IF(ISNUMBER(Y6),Z6-Y6,""))

Last edited:

#### jparks1015

##### New Member
Close! Just one error but I caught it. The formula added the lunch instead of subtracting it. Thank you sooooo much! You were a great help. Now I just need to decipher the code so I can understand how it worked.

Replies
4
Views
336
Replies
12
Views
158
Replies
7
Views
489
Replies
9
Views
174
Replies
0
Views
176

1,130,081
Messages
5,639,955
Members
417,120
Latest member
Pavithra devi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back