breaking down number according to defined parameters

Occasional Visitor

I'm working on a spread sheet to track employee hours and earnings for a summer program.

In columns B through W, I want to enter each employee's weekly hours, and have those hours separated out by funding source in 2 succeeding columns (X and Y)

Funding source X will pay for up to 20 hours of a staff member's time.

Funding Source Y will pay for any hours over 20 hours.


What formulas could I use in columns X and Y to separate the hours, using the above funding source parameters? e.g. I enter 23 hours in Column B, and X is populated with 20, and Y is populated with the remaining hours over 20.


Columns B through W will represent 1 week each, so I would duplicate whatever formula is used to total up the breakdowns over the summer.

1 Reply


In row 2, the hours up to 20 are =MIN(B2,20) and the hours over 20 are =MAX(B2-20,0)

To sum the hours up to 20 in B2:W2: =SUM(IF(B2:W2<20,B2:W2,20))

And the hours over 20 in B2:W2: =SUM(IF(B2:W2>20,B2:W2-20,0))