Point LedgerGuide
Attendance point tracker: columns, roll-off formula, limits
How to build an attendance point tracker in a spreadsheet: the columns, the rolling roll-off formula, and the point at which a sheet stops being enough.
An attendance point tracker needs two sheets: a log with one row per occurrence, and a roster that adds up each employee's points that are still inside the rolling window. If you already keep one, you can upload it to the free run below and check its balances.
The sheet is the cheap, common way to do the job, and it works until a few specific things happen. This guide covers the build and where it breaks.
What columns does an attendance point tracker need?
Keep the occurrence log flat, one row per occurrence:
| Column | Example | Why |
|---|---|---|
| Employee ID | 1042 | Names repeat and change |
| Employee name | Dana Whitfield | For reading |
| Date | 2026-06-01 | The start of the window |
| Type | Absence | Looks up the points |
| Points | 1 | Filled from a points table |
| Roll-off date | 2027-06-01 | Date plus the window |
| Protected leave | Yes / No | Yes scores zero |
| Entered by | Supervisor name | For who to ask |
| Note | Free text, keep medical detail out |
Put the points per type on a small table on a separate tab, so one change updates every row.
How do I calculate the roll-off date?
Add the window in months to the occurrence date. In Excel or Google Sheets, with the date in C2 and the window in months on a settings tab:
=EDATE(C2, Settings!B1)
EDATE handles month lengths. Be careful about 29 February dates and 31st-of-month dates, and check the result on a few of them before you trust the column.
How do I total only the points that still count?
Use a sum that skips rolled-off rows and protected leave. With the employee in A2 on the roster, points in column E of the log and roll-off dates in column F:
=SUMIFS(Log!E:E, Log!A:A, A2, Log!F:F, ">"&TODAY(), Log!G:G, "No")
That adds points for one employee whose roll-off date is still in the future and whose leave is not protected. Then look up the step with a step table, and show how many points are left to the next one.
What goes wrong with a spreadsheet tracker?
Spreadsheets break in predictable ways:
- Several copies. Each site or supervisor keeps their own, and the totals drift.
- Fixed ranges. A formula that covers rows 2 to 500 stops counting at row 501.
- Typing over a formula. One manual number in a roll-off cell keeps a point counting for a year.
- No history of changes. You cannot show when a point was added, or by whom.
- No notices. Each write-up is typed from scratch from the log.
A tracker is enough when one person enters everything and the roster is small. It starts to cost time when several supervisors report, when you have more than one site, or when someone has to check every row before a meeting.
How do I roll the balance forward each day?
The TODAY() in the total formula makes the sheet recompute whenever it is opened, so the balance is right on the day you look. The trouble is the day you do not look: a warning drafted from a stale printout can use a total from a week ago. Check the as-of date on every sheet that leaves the building, and print the roll-off dates next to the balance so a reader can see which points were counted.
If you want a notice-ready summary, add two columns to the roster: the step the balance reaches, found by a lookup against the step table, and the points left before the next step. Sort the roster by that last column and the people closest to a step come to the top.
What should I do when the sheet stops working?
You have two choices: tighten the sheet, or move the job off it.
To tighten the sheet, keep one master file in one place, protect the formula columns, and let supervisors send occurrences to one person to enter. To move off it, load the log into a tool that does the roll-off for you. See attendance point system software for how the main options compare.
Point Ledger takes your roster and occurrence history as CSV or Excel, so the sheet you have is the starting point. You confirm which column is which once and it is remembered. Balances and roll-off dates appear as soon as the history is in, supervisors log new occurrences on a no-login link, and balances recompute each night. It keeps a row per occurrence with who entered it and what changed.
If you are still setting the policy the sheet will implement, start with how an attendance point system works. To print a warning from the log, see the attendance write-up template.
Frequently asked questions
Is there a free attendance point tracker template?
Yes, many. The columns above are enough to build one, and the free run on this page accepts the same layout as a CSV or Excel file.
Can I do rolling points in Excel without macros?
Yes. EDATE for the roll-off date and SUMIFS for the total, as above, need no macros.
How do I stop employees from seeing each other's points?
Do not share the master file. Give each supervisor only their own employees, or collect occurrences by form and enter them centrally.
Every employee's attendance points, rolled off on the right day
Log each occurrence once. Point Ledger keeps each employee's rolling balance, the date each point rolls off and the next discipline step current, and drafts the warning notice when a threshold is crossed.
$79/ month
Get startedCancel any time.