Excel Guides

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.

TermPractice valueSource
ClientCL-01Terms!B2
New hours per calendar month10Terms!B3
Maximum new hours carried forward4Terms!B4
Opening September carryover0Terms!B5
ExpiryIncoming carry lasts for the next calendar month onlyTerms!B6
Use orderUse incoming carry before new monthly hoursTerms!B7
Time includedOnly CL-01 rows marked Yes in Counts to planTerms!B8

Follow the months in order

PeriodCalculationResult
September10 new − 4 used = 6 unused. Carry cap is 4.Carry 4; lose 2 above the cap.
October4 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 16th4 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 countedHoursSource rows
September: T-101 and T-1022 + 2 = 4‘Time Log’!A2:F3
October: T-103 and T-1041.25 + 0.75 = 2‘Time Log’!A4:F5
November: T-105, T-106 and T-1075 + 4 + 6 = 15‘Time Log’!A6:F8
T-108 internal adminExclude 1.5: Counts to plan is No‘Time Log’!A9:F9
T-109 for CL-02Exclude 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.

Leave a comment