Calculate difference with upper limit

[cdfal]

Hi everybody. Hoping someone could give me some help with the below query.

I need to find out how many hours workers are doing above their contract. These fall into two pay rates. Any hours done up to 37.5 is paid at one rate (Extra Time), and any hours over 37.5 (Over Time) is paid at another rate. I need to automatically split these hours into which pay band they fall.

For example, with a working formula, the two records below would display:
Record 1 - Over Time = 4.5 and Extra Time = 0.
Record 2 - Over Time = 22.5 and Extra Time = 17.5

The fields Contract, Over Time, and Extra Time should then be the same as Total Hours. I just need to split it.

Could someone help? Can it be done automatically?

 

[cdfal]

I know at some point you'll need to calculate what the threshold to take them from 'Extra Time' to 'Over Time' is (=37.5-Contact), but I'm not sure at what point this needs to go in.

Then, Extra Time's upper limit would be this threshold, with any additional hours then going into the Over Time column.

 

[jtakw]

Hi,

Using two formulas keeps it simple:

Excel 2010
	A	B	C	D	E
1	Name	Contract	Over Time	Extra Time	Total Hours
2	Joe	37.50	4.50	0.00	42.00
3	Jack	20.00	22.50	17.50	60.00
Sheet1

Cell Formulas
Range	Formula
C2	=E2-37.5
D2	=37.5-B2

Formulas copied down.

 

[cdfal]

Hi - thanks for the quick reply. Not quite what I'm looking for.

Essentially, Extra Time will equal Total Hours - Contact, BUT only up to 37.5 when the Contract is added.

The remainder (hours over 37.5) would then go into Over Time.

 

[jtakw]

Well, that's exactly what my sample (according to your OP), and your description, is doing.

May be you need to provide another sample with various scenarios.

 

[jtakw]

Since you're not providing more data to show what you need, I've created the following, adding a couple of scenarios, see if it suits your needs:

Excel 2010
	A	B	C	D	E
1	Name	Contract	Over Time	Extra Time	Total Hours
2	Joe	37.50	4.50	0.00	42.00
3	Jack	20.00	22.50	17.50	60.00
4	Jane	40.00	20.00	0.00	60.00
5	Bob	20.00	0.00	15.00	35.00
Sheet1

Cell Formulas
Range	Formula
C2	=IF(E2<37.5,0,MIN(E2-37.5,E2-B2))
D2	=IF(B2<37.5,MIN(37.5-B2,E2-B2),0)

Formulas copied down.