What should an attendance sheet format in Excel include?

Employee ID and name, one column for each day of the month, a status code for every day, and totals for present, absent, leave, week-off and holiday. For payroll you also need loss-of-pay days and paid days, which this register calculates.

How do I count attendance in Excel?

Use COUNTIF for a single row, for example =COUNTIF(C5:AG5,"P"). The template uses SUMPRODUCT with the day-number row so that columns beyond the month length (day 31 in a 30-day month) are ignored even if someone types in them.

How is a half-day treated for salary?

As half a day of loss of pay unless your policy says otherwise. The register counts each HD as 0.5 LOP and 0.5 paid.

Are week-offs and holidays paid when I am absent around them?

That is a policy choice. Some employers treat a week-off or holiday sandwiched between two unapproved absences as loss of pay. The calculator above has a field for it. Whatever you choose must be written in the appointment letter or HR policy, and applied the same way to everyone.

Which divisor should I use to cut salary for absence?

The one in your appointment letter. Calendar days is the cleanest because week-offs and holidays are already inside the month. If you divide by 26 working days, do not also count week-offs as paid days, or you will deduct more than intended.

When should I stop using an Excel attendance sheet?

When marking, correcting and reconciling it takes more time than the pay run, when people dispute entries, or when you have field or remote staff whose location matters. At that point attendance should be captured automatically.

Free download - built for payroll

Attendance sheet format in Excel that feeds payroll

Most attendance sheets stop at counting P and A. This monthly register carries on: it flags unmarked days, works out loss-of-pay days and paid days, and shows the salary after LOP, so the number you type into the payslip is the one the attendance supports.

For teams of 5 to 500 employees. No signup, no watermark.

Download

Pick an attendance template

Monthly attendance register with payroll link

Best for: Teams of 5 to 500 where HR marks attendance once a month for payroll.

One row per employee, a column for every day, codes P, HD, L, WO, H and A. Formulas count each code, flag unmarked days, and give LOP days and paid days. A Payroll link sheet shows the salary after LOP.

Download .xlsx

Daily attendance sheet with in/out time

Best for: Shift teams, shops and sites that record punch times.

Enter in and out times. Hours worked, late mark (with grace minutes) and overtime beyond standard hours calculate from settings you control.

Download .xlsx

Attendance to payroll

Convert attendance into paid days and salary cut

Result

27.5 paid days

2.5 loss-of-pay days out of 30. Type 27.5 in the Days paid cell of the salary slip.

Calendar days (30): salary cut: ₹4,167
Fixed 30 days: salary cut: ₹4,167
26 working days: salary cut: ₹4,808

Use the divisor in your appointment letter. In the online salary slip generator, enter 2.5 as the loss of pay days and pick the same divisor.

Take the paid days into the salary slip generator, the salary slip Excel template or the salary sheet for the whole team.

Codes

Attendance codes and how the register counts them

P
Present
Counts as paid.
HD
Half day
0.5 paid and 0.5 loss of pay.
L
Paid leave
Paid. The balance is tracked in your leave ledger.
WO
Week off
Paid when you use the calendar day divisor.
H
Holiday
Paid when you use the calendar day divisor.
A
Absent
Loss of pay.

Paid days are days in the month minus loss-of-pay days, which matches the calendar-day divisor in the salary slip templates. Days left blank are reported as unmarked; a setting decides whether they count as loss of pay.

Where attendance goes wrong

Six mistakes that turn into payroll disputes

1. Counting half-days as full days or full absences

A half-day is 0.5 loss of pay. Rounding it up or down in either direction, month after month, adds up to real money across a team.

2. An unwritten sandwich rule

Cutting pay for a week-off between two absences is a policy, not an automatic rule. If it is not in the appointment letter or handbook, expect the first dispute to go against you.

3. Dividing by 26 but paying week-offs too

With 26 working days as the divisor, week-offs are not paid days. Count them as paid in the register and you deduct for the same absence twice. Pick one divisor and make the attendance sheet and payslip agree.

4. Blank cells that silently become paid days

If nobody marks day 17, the sheet treats it as nothing, and the employee is paid. The register counts unmarked days so you can chase them before payroll closes.

5. Overwriting missed punches

When a punch is missed, record the correction with a reason and who approved it instead of silently changing the entry. It is the difference between a regularisation and an edit nobody can explain.

6. Attendance that disagrees with the leave ledger

If L is marked for someone with no leave balance, either the leave is unpaid or the ledger is wrong. Reconcile the two before the pay run, not after the payslip goes out.

Build it yourself

Attendance sheet formulas in Excel

Count a code across the month (ignores columns past the month length)
=SUMPRODUCT((C4:AG4<=DaysInMonth)*(C5:AG5="P"))
Unmarked days
=DaysInMonth-(P+HD+L+WO+H+A)
LOP days
=Absent+0.5*HalfDays
Paid days
=DaysInMonth-LOPDays
Attendance % of working days
=(P+0.5*HD+L)/(DaysInMonth-WO-H)
Salary after LOP
=ROUND(Gross*PaidDays/DaysInMonth,0)

Add Data Validation (Data, Data Validation, List) with P, HD, L, WO, H, A on the day cells so nobody types a code the formulas do not recognise, and conditional formatting to colour A red and WO grey.

Beyond Excel

When the sheet stops scaling

Excel is fine while one person marks attendance for a small team. It breaks when staff work from different places, when you need proof of where a punch happened, or when corrections need approvals. This page is part of Zeba's attendance management system. See how attendance management software, geofenced attendance and shift management remove the retyping, and how leave management keeps the ledger in step with payroll.

Got questions?

Attendance sheet in Excel: FAQs

Codes, formulas, half-days and divisors.

Employee ID and name, one column for each day of the month, a status code for every day, and totals for present, absent, leave, week-off and holiday. For payroll you also need loss-of-pay days and paid days, which this register calculates.
Use COUNTIF for a single row, for example =COUNTIF(C5:AG5,"P"). The template uses SUMPRODUCT with the day-number row so that columns beyond the month length (day 31 in a 30-day month) are ignored even if someone types in them.
As half a day of loss of pay unless your policy says otherwise. The register counts each HD as 0.5 LOP and 0.5 paid.
That is a policy choice. Some employers treat a week-off or holiday sandwiched between two unapproved absences as loss of pay. The calculator above has a field for it. Whatever you choose must be written in the appointment letter or HR policy, and applied the same way to everyone.
The one in your appointment letter. Calendar days is the cleanest because week-offs and holidays are already inside the month. If you divide by 26 working days, do not also count week-offs as paid days, or you will deduct more than intended.
When marking, correcting and reconciling it takes more time than the pay run, when people dispute entries, or when you have field or remote staff whose location matters. At that point attendance should be captured automatically.

Have more questions?

More free tools

Related to attendance and leave, and the rest of the toolkit.

See all free HR and payroll tools

Stop chasing attendance before every pay run. Zeba captures attendance automatically and feeds paid days and LOP straight into payroll.

Start free - no credit card See Zeba payroll