# Subtracting a varied break times on a time sheet.

0

### I need help, I am doing a time sheet that contains shift work and allowances and lunch times that vary.

The formula I have which allows me to manually input the break times is :

• Start time D6
• Breaks E6 .... needs to be manually entered as it can vary from 30min to 1.5 hours
• Finish time F6

=ROUND((IF(OR(\$D\$6="",\$F\$6=""),0,IF(\$F\$6<\$D\$6,\$F\$6+1-\$D\$6,\$F\$6-\$D\$6))-\$E\$6/1440)/(1/1440),0)*(1/1440)    this works fine,

but then I have a formula for shift allowance

=(F6<D6)*("21:30"-"14:30")+MEDIAN(F6,"14:30","21:30")-MEDIAN(D6,"14:30","21:30")

which adds up fine too but I need to add a formula for (breaks) E6 to the shift allowance  formula

### Discussion

I would use a Date/Time format (e.g 5/22 14:00) for start/end times and a simple time format (e.g 0:30) for the break time which would eliminate much of the complexity of your first formula. It would then simply be =F6-E6-D6. As for the second formula for shift allowance, I'm a bit lost. My understanding of shift allowance is to pay more for off shifts. Is that the goal here?
gebobs (rep: 30) May 22, '17 at 10:51 am
thankyou for reminding me KISS is better ( Keep it sort and simple)
I simplified the first formula to
=MOD(F6-D6-E6,1)*24    as there are Day, Afternoon and Night shifts I added the MOD function and it works. As for the 2nd formula, we get paid and allowance for different shifts EG:
Afternoon shift from 14:30 to 21:30 we are paid and additional 12.5% and an 15% for night shift 21:30 to 7:00 on top of my base wage rate less breaks. So I left the 2nd formula almost the same just minused E6
=(F6<D6)*("21:30""14:30")+MEDIAN(F6,"14:30","21:30")MEDIAN(D6,"14:30","21:30")-E6
and it works too only now the cells that do not fall within this range so ########## and a message saying negitave times are to long, not sure how to add the mod function to this formula,
debrahilton May 22, '17 at 11:35 pm
I would like to see a current version of the template you use where you show your inputs with desired results and the current formulas that you use.
don (rep: 1900) May 23, '17 at 9:14 am
sorry I have tried to copy and paste but this is not allowing me to.
debrahilton May 23, '17 at 11:35 pm
To do that, you edit your question and upload an Excel file with the template in it.
don (rep: 1900) May 24, '17 at 1:37 am
Hi, I have uploaded the file as you said.... thankyou again for explaining what to do..
some how I need to get the formula to work on 24hrs as the above formula with spicific times does not work in the night shift column "21:30""7:00"