time based calculation

[genegammage]

Hi There,

I am in desperate need of some urgent help. My Company has overpaid a large number of employees, and I am trying to put together a spreadsheet that will help people calculate how much they should have been paid.

I have managed to calculate most of what we need, but there is a column that I need that will total up OT hours.

Overtime is only payable between the hours of 23:00 and 06:00, there is a call out for the first three hours, and a max of 4 hours OT.

I have been banging my head against a brick wall for almost a week, and getting rather frustrated, so you have no idea how much I would appreciate some genius working it out for me.

Many people will appreciate the solution.

Thank

[darkyam]

Not familiar with the term "callout". What do you mean? Also, what are the rules for OT? Is it more than 8 hours in a given shift with the last hour(s) being between those times?

[genegammage]

Thanks for the reply....

A Callout covers the first 3 hours of a shift between 23:00 and 06:00, so the Max OT that you can claim is the 4 hours between 02:00 and 06:00, as the callout covers the time beween 23:00 and 02:00. Shifts can start or end at any time between 23:00 and 06:00, but the first 3 hours will always be covered by the callout.

I hope this helps, I have put a number of examples into the attached sheet.

Thanks again

[darkyam]

I appreciate the examples, but I'm still confused. By the rules as I understand them, shouldn't the second one listed be 2 hours of OT? If the shift was midnight to 5:00, then three hours are in the callout and the others are OT, right? How is it different from the one on Row 13, where there are 4 hours of OT?

[genegammage]

Yeah, sorry, my bad. This thing has had me in knots, sorry.....

[darkyam]

Try this formula: =MIN(4,MAX(0,(IF(E7>=1/12,MOD(E7-D7,1)-1/8)-MAX(0,23/24-IF(D7=0,1,D7)))*24)).

[genegammage]

Hi,

Thanks, that forumla is was over my head, you make it look so easy.
all the examples seem to work, but when I change the last one, line 24, from 00:00 to 12:00 the answer is 4. If I change it to 02:00 to 12:00 the anwer comes out as 0.

Am i doing something wrong ??

[darkyam]

After banging my head on it for a while, this seems to work on all your examples:
=MIN(4,MAX(0,(IF(E7>=1/12,MOD(E7-D7,1)-1/8)-IF(E7>1/4,E7-1/4,MAX(0,23/24-IF(D7=0,1,D7))))*24))

[genegammage]

AMAZING, you have no idea how much this will be appreciated, it looks like it works on all to me, i had no idea where to turn, thanks SO SO much.

Looking at the formula, I would never have got there, you have saved alot of people alot os stress, thanks

[contaminated]

Hi. In C7 use this
=IF(OR(WEEKDAY(B7)={2,3,4,5,6}),"W",IF(OR(WEEKDAY(B7)={7,1}),"S","0"))

In F7 this function
=MOD(E7-D7,1)*24

In K7 this function
=AND(NOT(E7>IF(D7=0,"24:00",D7)),MOD(E7-"23:00",1)>TIME(3,0,))*MOD(IF(E7>TIME(6,0,),TIME(6,0,),E7)-(IF(IF(D7=0,"24:00",D7)<"23:00","23:00",D7)+"3:00"),1)*24

Copy all down