I have a spreadsheet that tracks benefit time at work.


We are allowed to accumulate 40 hours of compensation time after that we have to take it in overtime.
what i need is a formula that quit accumulating at 40 then another that takes the excess each pay period and tracks that.
I have attached a sheet to show what i mean.

Thanks, Jerry.

Attached Files

Benifit Time-2017-1.xlsx (65.0 KB, 13 views)

The sheet you attached doesn't necessarily show what you mean. Can you re-post the attachment showing expected result samples so that we understand what you are looking for.

Sorry about that.
I attached the document before saving changes.

Attached Files

Benifit Time-2017-1.xlsx (65.9 KB, 11 views)

Try:

In E7:

=MIN(40,D7*1.5)

in G7:

=E7-F7

in H7:

=MAX(0,D7*1.5-40)

Got er done.
Thanks you were a big help.