Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

are there any solutions.... please

excelformatting
MS Office Version: debrahilton
posted by: debrahilton on Mon May 22, 2017 at 4:07 am
Answer
Discuss

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,
any idias please.
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: 1989) 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: 1989) 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"
Ps I uploaded the file on the first question asked...
debrahilton May 24, '17 at 8:40 am
Add to Discussion

Answers

0

i'm more the fan of Decimal time (8:15am = 8.25; 3:45 pm = 15.75) as long as things are worked out in quarter of hours, like :15, :30 & :45 (.25, .5, .75), unless individuals work past midnight....it keeps things simple

posted by: queue (rep: 467) on Mon May 22, 2017 at 4:34 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login