Free resource · Excel + data validation
An attendance sheet template that actually computes.
Present, half-day, late and loss-of-pay counts run on live COUNTIF formulas, not a static grid. Download the .xlsx, no email required.
- No base fee
- Per active employee
- GST extra
Present Days, Half Days, Late Count, LOP Days and Total Payable Days recalculate the moment a status dropdown changes.
Real released UI screenshots remain required before public indexing.What's inside
The Attendance tab, with sample data.
Three employees shown here for scale. The real file ships with 15 blank employee rows you extend by copying the last block.
- PPresent
- AAbsent
- HDHalf Day
- LLeave
- WOWeekly Off
- HHoliday
Scroll the table sideways to see every column.
| Emp ID | Employee Name | 1 | 2 | 3 | … | Present Days | Half Days | Late Count | LOP Days | Total Payable Days |
|---|---|---|---|---|---|---|---|---|---|---|
| E001 | Asha Verma | P | L | HD | … | 18 | 3 | 4 | 2 | 27.5 |
| E002 | Employee 2 | P | WO | P | … | 22 | 0 | 0 | 0 | 26 |
| E003 | Employee 3 | A | P | P | … | 20 | 0 | 1 | 1 | 25 |
| Team total | 60 | 3 | 5 | 3 | 78.5 |
Sample data. Asha's month includes 3 approved leave (L) days, and they count as payable: Total Payable Days = Present + Half×0.5 + Leave + Weekly Off + Holiday. Download the file to see the live COUNTIF formulas in each cell.
What each column does
- Present Days
- =COUNTIF(day range, "P"). Counts exact P cells only, and recalculates the moment you change a status.
- Half Days
- =COUNTIF(day range, "HD"). Half-day marks stay a separate count from full-day presence.
- Late Count
- Counts the independent Late? sub-row (Y/N), so a day can be Present and late at the same time. Late is a flag, not a 7th status.
- LOP Days
- =COUNTIF(day range, "A"). Absent counts as Loss-of-Pay by default. Rename or remap this column if your policy pays out some absences; the sheet never asserts a statutory LOP rule.
- Total Payable Days
- = Present + (Half x 0.5) + Leave + Weekly Off + Holiday. Approved paid leave (L), weekly-offs and holidays all count as payable without a punch. Only A, an uncovered absence, is the loss-of-pay candidate, so it stays out of this total.
Download
Get the .xlsx, no email, no account.
One file, one reusable tab. Duplicate it each month by changing the Month and Year cells, or by copying the tab.
Column by column
What each part of the sheet does.
- Status legend (row 5)
- P, A, HD, L, WO, H: the six codes every day cell is restricted to via a data-validation dropdown. Late is deliberately not a seventh code; see below.
- Late? sub-row
- A second, independent Y/N row under each employee. A day can be marked Present and flagged late at once, which mirrors how a day-status engine treats lateness as a flag rather than its own status.
- Company calendar row
- One shared row marking WO and H for the whole team. It drives the self-adjusting Working Days in Month helper cell, so the team % doesn't need a hardcoded 30 or 26.
- Holiday reference table
- A blank Date / Holiday name table below the grid. It ships empty on purpose: a pre-filled national holiday list would be a statutory claim this file doesn't carry a source or effective date for. Fill it in from your own HR calendar.
How to use the formulas
From a blank month to a summary row.
- Set Month, Year and Company name. These three cells at the top drive every day-column header; fill them once per month.
- Mark each day's status from the dropdown. P, A, HD, L, WO or H only; free text isn't accepted, which is what keeps the COUNTIF formulas trustworthy.
- Flag late arrivals on the Late? row. Independent of the day's status: a late Present day still counts as Present.
- Read the five summary columns. Present Days, Half Days, Late Count, LOP Days and Total Payable Days update per employee, and a team row at the bottom totals them with a Team Attendance % figure.
Paid leave, half-days and loss-of-pay
A worked example.
Mark HD on the 14th and Present Days doesn't drop. A half day isn't an absence; it's a separate count weighted at 0.5 in Total Payable Days. Mark L for an approved leave day and it counts too: Total Payable Days = Present + Half×0.5 + Leave + Weekly Off + Holiday, so approved paid leave is paid, not silently dropped. Take Asha's month above: 18 present, 3 half-days, 3 approved leave days and 5 weekly-offs and holidays give 18 + 1.5 + 3 + 5 = 27.5 payable days.
LOP Days only increments for a full A, an uncovered absence. That is the one status the payable total leaves out. Absent defaults to Loss-of-Pay in this sheet; a comment on the column header flags that this is a default, not a statutory rule, and can be remapped if your policy pays out some absences differently.
Honest limits
When the spreadsheet breaks.
Two thresholds tend to show up first. Once cross-checking each employee's day-status by eye takes longer than marking it, roughly 15 to 20 employees in practice, manual dropdown-marking starts missing errors, and this sheet has no undo or audit trail to catch them. Once late-marks, half-days and regularizations pass about 1 in 10 marked days per cycle, the flat COUNTIF formulas can't show why a day was marked what it was: there's no reproducible, replayable policy-version trail behind the number.
That reason trail is what LekhaHR's attendance system is built for: the same day-status decision, versioned against the policy that produced it, visible to the employee, manager and HR audit alike.
FAQ
Questions before you download.
How do I create an Excel sheet for employee attendance?
Download the template, open the Attendance tab, and set the Month, Year and Company name cells at the top. Everything else, including the day columns, weekday labels and formula columns, updates from those three cells, so you fill them once per month rather than rebuilding the grid.
What is the formula for attendance count or percentage in Excel?
Present Days uses =COUNTIF(day-range,"P"), Half Days and LOP Days use the same COUNTIF pattern against "HD" and "A". Team Attendance % divides the summed Present Days by employee count times the working days in the month, and that working-days figure self-adjusts for weekly-offs and holidays instead of being hardcoded to 30 or 26.
What's the best format for a monthly attendance sheet?
One row per employee with a data-validation dropdown per day, not free text. That's what makes the count formulas reliable: this template restricts each day cell to P, A, HD, L, WO or H so a stray keystroke can't silently break a COUNTIF.
How do I record a half-day in the attendance sheet?
Mark the day HD instead of P. The Half Days column counts it separately, and Total Payable Days weights it at 0.5. Present Days itself doesn't drop, since a half day isn't an absence.
How do I handle late arrivals without a separate absence code?
Late is a Y/N flag on its own sub-row under each employee, independent of the day's P/A/HD/L status. A day can be Present and flagged late at once, which matches how LekhaHR's own day-status engine treats lateness: as a flag on a present day, not a distinct day type.
Is this attendance sheet template free, and do I need to sign up?
Yes. It's a direct .xlsx download with no email form and no account. Open it, duplicate the tab or the file each month, and it's yours.
When should I move from a spreadsheet to attendance software?
Two thresholds tend to show up first. Once cross-checking each employee's day-status by eye takes longer than marking it, roughly 15 to 20 employees in practice, manual dropdown-marking starts missing errors. Once late-marks, half-days and regularizations pass about 1 in 10 marked days per cycle, the sheet's flat COUNTIF formulas can't show why a day was marked what it was.
Does this template calculate salary or payroll?
No. It counts present, half, late and LOP days only. Turning attendance into pay is a payroll calculation this sheet deliberately leaves out.