Work Schedule with lunch breaks if over 8hrs

jparks1015

New Member
Joined
Nov 27, 2017
Messages
10
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
Joined
Feb 6, 2012
Messages
1,791
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
Joined
Nov 27, 2017
Messages
10
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top