Calculating current number of hours worked

**Nickolai** · 03-01-2015, 04:23 PM

I'm working on a "schedule calculator". The business owner puts in the schedule for the week and gets a projected labor cost. I figured out the formula for calculating overtime at the end of the week but my problem is in the middle of the week. At the bottom of each day is a total hours worked and cost of labor. But if someone is on overtime on Thursday, the total for that day would be higher. What kind of a formula would I need to check if they are on overtime and then multiply by 1.5 only for those hours past 40? This is the last step for this project and I am stumped. Any help would be much appreciated. Attached is the workbook I'm building.

Schedule Calculator (original).xlsx

**FDibbins** · 03-01-2015, 05:34 PM

In your sample, the EE worked 40 hours for the week (assuming they did not work for more days), so they only had 40 hours for the week - which would imply no OP payment. However, they worked 4 (?)) hours OT on 3 days, so they actually worked 12 hours OT.

See if you can use this for the Payment calc columns (I tested it in J4)...
=IF(I4="","",$B4*24*IF(I4<=0.3333,I4,0.3333+(I4-0.3333)*1.5))

On a side note, you can use this for the SUM in AE4...
AE4=IF(SUMIF($C$1:$AD$3,"Hours Worked",$C4:$AD4)*24>40,40/24,SUMIF($C$1:$AD$3,"Hours Worked",$C4:$AD4))
and for the OT, use this...
AF4=SUMIF($C$1:$AD$3,"Hours Worked",$C4:$AD4)-(COUNT(AD4,Z4,V4,R4,N4,J4,F4)*8/24)

**protonLeah** · 03-01-2015, 06:20 PM

Here's one with additional columns for Monday (only)

**Nickolai** · 03-02-2015, 07:16 PM

Thank you both for the responses, but we calculate OT by a 40 hour week, not an 8 hour day. So I need it to check the total for the week each day and multiply appropriately. FDibbins, this is why the last column subtracts 40. I believe I'm on the right path now, protonLeah, it may be easier to add additional columns. Will let you guys know when I figure it out.