Mar 28 2019 09:54 PM
Balance carried forward from 2018 | 21.00 | |||||
MONTH | HOURS EARNED | HOURS USED | HOURS USED | HOURS USED | HOURS USED | BALANCE |
January | 1.75 | 0.00 | 0.00 | 0.00 | 0.00 | 22.75 |
February | 1.75 | 2.00 | 0.00 | 0.00 | 0.00 | 22.50 |
March | 1.75 | 0.00 | 4.00 | 0.00 | 0.00 | 20.25 |
April | 1.75 | 5.00 | 0.00 | 0.00 | 0.00 | 17.00 |
Balance carried forward from 2018 | 21 | |||||
MONTH | HOURS EARNED | HOURS USED | HOURS USED | HOURS USED | HOURS USED | BALANCE |
January | 1.75 | 0 | 0 | 0 | 0 | =SUM((G5+B7)-SUM(C7:F7)) |
February | 1.75 | 2 | 0 | 0 | 0 | =SUM(G7+B8)-SUM(C8:F8) |
March | 1.75 | 0 | 4 | 0 | 0 | =SUM(G8+B9)-SUM(C9:F9) |
April | 1.75 | 5 | 0 | 0 | 0 | =SUM(G9+B10)-SUM(C10:F10) |
Each month 1.75 hours are earned, each week time is subtracted from the previous month’s balance. The maximum number of hours that can be accumulated is 21.
I figured out that =IF(G7>=21,"21") will have it return 21 if the balance is over 21 hours. I can’t seem to figure out how to have it show actual number of hours when they are less than 21. I probably need to add a then statement but I can’t get it added without an error.
Mar 29 2019 01:09 AM
@Tlang2010 , you may use something like
=MIN(SUM(<hours>,21)
Mar 29 2019 04:19 AM