How to Check Retainer Hours Rollover and Expiry in Excel
A client used four hours in September and two in October. Their plan includes ten hours each month. In November, you log fifteen hours. If your spreadsheet carries every unused hour forward, it shows nine hours still available.
Under the fictional terms in this practice workbook, November has 14 hours available and 15 hours used through November 16. One hour is above the allowance. The difference comes from the four-hour carry cap and expiry of incoming hours.
Download the retainer rollover workbook
Download Jolene_Retainer_Rollover_Practice.xlsx
All clients, time entries and agreement terms are synthetic. This is an hours calculation exercise. Whether any additional charge is allowed depends on the actual agreement and required approval.
Start with the client’s written rule
There is no single rollover rule to assume. TrackingTime documents manual handling of unused hours. Jasper documents oldest-first usage and an expiry ledger. For this exercise, use only the fictional Terms tab.
| Term | Practice value | Source |
|---|---|---|
| Client | CL-01 | Terms!B2 |
| New hours per calendar month | 10 | Terms!B3 |
| Maximum new hours carried forward | 4 | Terms!B4 |
| Opening September carryover | 0 | Terms!B5 |
| Expiry | Incoming carry lasts for the next calendar month only | Terms!B6 |
| Use order | Use incoming carry before new monthly hours | Terms!B7 |
| Time included | Only CL-01 rows marked Yes in Counts to plan | Terms!B8 |
Follow the months in order
| Period | Calculation | Result |
|---|---|---|
| September | 10 new − 4 used = 6 unused. Carry cap is 4. | Carry 4; lose 2 above the cap. |
| October | 4 incoming − 2 used = 2 old hours left. All 10 new hours remain unused. | Old 2 expire. Carry 4 new hours; lose 6 above the cap. |
| November through the 16th | 4 incoming + 10 new = 14 available. Usage is 15. | 0 currently remain; 1 hour above allowance. |
October drops eight hours in total: two incoming hours expire and six new hours exceed the carry cap. Check 'Hours rollover'!F7 and J7. These are separate reasons, so keep both visible when explaining the balance to the client.
Check which time entries count
The Time Log uses decimal hours: 0.75 means 45 minutes.
| Time counted | Hours | Source rows |
|---|---|---|
| September: T-101 and T-102 | 2 + 2 = 4 | ‘Time Log’!A2:F3 |
| October: T-103 and T-104 | 1.25 + 0.75 = 2 | ‘Time Log’!A4:F5 |
| November: T-105, T-106 and T-107 | 5 + 4 + 6 = 15 | ‘Time Log’!A6:F8 |
| T-108 internal admin | Exclude 1.5: Counts to plan is No | ‘Time Log’!A9:F9 |
| T-109 for CL-02 | Exclude 5: another client | ‘Time Log’!A10:F10 |
If using a time-tracking export, preserve client IDs, work dates, decimal hours and the eligibility decision. Check whether time was rounded. Harvest’s time report documentation describes CSV and Excel exports and its rounding settings. The supplied file is synthetic, not an export from a live Harvest account.
Keep the calculation steps separate
The Hours rollover sheet first totals qualifying time by client and month with SUMIFS. Carry used is the smaller of incoming hours and usage. Old hours expire is incoming carry less carry used. Remaining usage consumes the new allowance. Carry to next is the smaller of unused new hours and the cap. Over allowance is usage above incoming carry plus new hours.
The formulas cover the supplied rows and three months. November contains no time after November 16. The SUMIFS date ranges cover whole calendar months, so filter later entries or add an explicit cutoff when reviewing a historical position. Extend ranges for a larger export. Expiry columns describe the month-end outcome at the recorded usage level; later work can change it.
Try the source tabs in Jolene.pro
Open the workbook in Jolene.pro and select Time Log and Terms.
Use the fictional Terms to calculate CL-01’s monthly hours from September through November 16, 2026. Show incoming carry, new hours, qualifying usage, carry used, incoming hours expiring at month end, new hours carried after the cap, and hours above allowance. Explain October’s expiry and cite the cells. Exclude other clients and rows that do not count to the plan.
Expected results: September carry 4, October carry 4, and November usage 15 against 14 available. The figures and cell references were independently checked against the synthetic workbook. A live Jolene.pro result has not been verified.
For a different hours review, see forecasting a fixed-fee project overrun before actual hours cross the budget. Browse more Workbook Studies or try your own workbook at Jolene.pro.