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:

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,194
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,099,281
Messages
5,467,733
Members
406,549
Latest member
midcoastchris04

This Week's Hot Topics

Top