Admin time tracker
A spreadsheet for measuring how long an admin task actually takes, before and after you change it. Median, not average, and corrections alongside the minutes.
Without a baseline, every claim about time saved is an impression. This is the spreadsheet for getting one.
Download it, or copy the layout below. The method is in measuring an admin task before you change it.
How to use it
- Define the task in one sentence, with a clear start and end. Put it at the top of the sheet. “From opening the referral email to saving the summary in the system.”
- Record each time someone does it. Rough times to the nearest minute are fine.
- Aim for at least 20 records over two weeks, so you catch a normal spread of days.
- Use the median, not the average. One chaotic afternoon drags an average a long way.
- Record corrections too. A faster process that produces more errors is not an improvement.
- Repeat it after the tool has settled — a few weeks in, not on day three.
The columns
| Column | What goes in it |
|---|---|
| Date | The day |
| Who | Initials are enough |
| Phase | Before or After |
| Start | Time started |
| End | Time finished |
| Minutes | =(E2-D2)*1440, or just type it |
| Needed correcting? | Yes or No |
| What needed fixing | Free text. This column is where the useful findings come from |
| Notes | Anything unusual about that instance |
The summary
There is no MEDIANIFS, so give yourself two helper columns and the rest is straightforward. In column K put =IF($C2="Before",$F2,"") and in column L put =IF($C2="After",$F2,""), then fill both down. MEDIAN, MIN and MAX ignore the blanks.
- Median minutes:
=MEDIAN(K:K)for before,=MEDIAN(L:L)for after - Fastest and slowest:
=MIN(K:K)and=MAX(K:K)— the spread matters as much as the middle - Correction rate:
=COUNTIFS($C:$C,"Before",$G:$G,"Yes")/COUNTIF($C:$C,"Before")
Avoid =MEDIAN(IF($C:$C="Before",$F:$F)), which is the formula most people reach for first. It is an array formula: older versions of Excel need Ctrl+Shift+Enter and Google Sheets needs ARRAYFORMULA, and without either it returns a number that looks plausible and is wrong. The helper columns work everywhere.
The downloadable version has all of this already set up.
What to report at the end
Three numbers and one sentence:
- Time before — median, with the range
- Time after, including checking — median, with the range, and when you took it
- Corrections — before and after
- What you could not measure — and there is always something. Record quality, staff confidence, whether the saved time went anywhere useful
That last line is what makes the rest credible.
A warning worth repeating
Time saved on drafting only counts if the drafting-plus-checking time is lower than the original writing time. Measure the whole task, end to end, or the number means nothing.
If a care worker saves six minutes and spends four checking, the saving is two minutes. That is still worth having. It is not the number in the sales deck.