Hello,
I'm having issues with a formula to calculate pay depending on what time you work.
I've attached my sheet. For many of the punches it will correctly calculate the standard hours and the peak hours. Standard hours being between 6AM - 4PM with peak being everything else.
My standard formula with start cell F4 and End cell G4 is:
=24*IF(F4="","0",(IF(MOD(F4,1)<MOD(G4,1),MIN(MOD(G4,1),K33)-MAX(MOD(F4,1),K34),MAX(0,K33-MOD(F4,1))+MAX(0,MOD(G4,1)-K34))))
And my peak hours formula is:
=24*(G4-F4)-H4
K33 being peak hours at 4:00 PM and K34 being standard at 6:00 AM.
With my sheet if I start at 10AM and end at 10PM it correctly calculates 6 standard hours and 6 peak hours. However if I start at 5PM and end at 10PM it calculates -1 standard hour and 6 peak hours. It gives me the correct amount of total hours, but messes up the pay.
I would also like to make the formula so it can calculate across two days. Example - 3PM to 1AM the next day. This should show 1 hour standard and 9 hours peak.
Any advice?
Bookmarks