At a glance
| Document type | Excel template |
|---|---|
| Kept by | Employer |
| Sheets included | Monthly attendance, Daily log, Weekly summary |
| Download format | Excel (.xlsx) |
| Statutory reference | Occupational Safety, Health and Working Conditions Code, 2020 |
| Last reviewed | 28 October 2026 |
| Maintained by | Engage HR editorial team |
Attendance sheet, muster roll and attendance register: which is which
The three names get used loosely. They are different records, kept for different readers.
| Record | What it is | Who reads it | Where this template fits |
|---|---|---|---|
| Monthly attendance sheet | A grid of employees against the days of the month, one code per cell, with totals. | Payroll, and the manager approving leave. | The first sheet in the download is this record. |
| Daily attendance log | In time, out time and break for each person on a given day, with hours worked. | Shift supervisors, and payroll where overtime is paid. | The second sheet. Use it where hours, not just presence, decide pay. |
| Muster roll | The list of persons employed with their attendance, kept as a statutory record. | The Inspector-cum-Facilitator on an inspection. | The monthly sheet carries the attendance a muster roll needs. The prescribed form adds particulars set by your state's rules. |
| Attendance register | The statutory register of attendance, hours, rest days, leave and overtime. | The labour department, and anyone auditing compliance. | Both sheets feed it. The form itself is prescribed by the appropriate Government, so copy the columns from your state's notified form. |
Standard attendance sheet format
A monthly attendance sheet has four blocks, left to right. Keep them in this order so the totals sit next to the grid that produces them.
- Header. Establishment name, the month and year, and the weekly off pattern. Without the month on the sheet, a saved copy is impossible to place later.
- Employee details. Serial number, employee code, name and department. The code is what payroll matches on, so it has to be the same code used in the salary sheet.
- The day grid. One column per calendar day, one code per cell, chosen from a fixed list: P for present, A for absent, L for paid leave, HD for a half day, WO for the weekly off, H for a holiday, OD for a day on duty away from the workplace and WP for a weekly off or holiday that was worked.
- Totals. Present, absent, leave, weekly offs, holidays, worked offs, a check column that counts the days marked, and the two figures payroll takes away: paid days and loss of pay days.
The check column is the one most sheets leave out. If it does not equal the days in the month, a cell has been left blank or typed wrongly, and the paid days figure is wrong with it. The exception is an employee who joined or left during the month, where the blanks are correct and the check shows exactly how many days they were on the rolls.
Preview of the sheets
Monthly attendance
Six employees for October 2026, with Sundays as the weekly off and 2 October as a holiday. Rahul Verma joined on 12 October, so his first eleven days are blank and his check column reads 20. Vikram Rao, the warehouse lead, was called in on Sunday 4 October, so that day is WP.
| S.No | Employee code | Employee name | Department | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | Present | Absent | Paid leave | Weekly off | Holidays | Worked offs | Days marked | Paid days | LOP days |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | E101 | Priya Sharma | Accounts | P | H | P | WO | P | P | P | P | P | P | WO | P | P | L | P | P | P | WO | P | P | P | P | P | P | WO | P | P | P | P | P | P | 25 | 0 | 1 | 4 | 1 | 0 | 31 | 31 | 0 |
| 2 | E102 | Arjun Nair | Sales | P | H | P | WO | P | P | P | A | P | P | WO | P | P | P | P | HD | P | WO | P | P | P | A | P | P | WO | P | P | P | P | P | P | 23.5 | 2.5 | 0 | 4 | 1 | 0 | 31 | 28.5 | 2.5 |
| 3 | E103 | Fatima Sheikh | Operations | P | H | P | WO | P | P | P | P | P | P | WO | L | L | P | P | P | P | WO | P | P | P | P | P | P | WO | P | P | P | P | P | P | 24 | 0 | 2 | 4 | 1 | 0 | 31 | 31 | 0 |
| 4 | E104 | Rahul Verma | Operations | P | P | P | P | P | P | WO | P | P | P | P | P | P | WO | P | HD | P | P | P | P | 17.5 | 0.5 | 0 | 2 | 0 | 0 | 20 | 19.5 | 0.5 | |||||||||||
| 5 | E105 | Sneha Kulkarni | Human Resources | P | H | P | WO | P | HD | P | P | P | P | WO | P | P | P | P | P | P | WO | P | P | P | P | P | P | WO | P | P | P | L | P | P | 24.5 | 0.5 | 1 | 4 | 1 | 0 | 31 | 30.5 | 0.5 |
| 6 | E106 | Vikram Rao | Warehouse | P | H | P | WP | P | P | P | P | P | P | WO | P | P | P | P | P | P | WO | P | P | P | P | P | P | WO | P | P | P | P | P | P | 26 | 0 | 0 | 3 | 1 | 1 | 31 | 31 | 0 |
- P = present. A = absent without approved leave. L = approved paid leave. HD = half day, counted as half present and half absent. WO = weekly off. H = paid holiday. OD = on duty away from the workplace, counted as present. WP = worked on a weekly off or holiday.
- Paid days = present + paid leave + weekly offs + holidays + worked offs. LOP days = absent days, including the absent half of each half day.
- A WP day is already a paid day off, so the work done on it is extra: it goes to overtime on the Weekly summary or to a compensatory off.
Shaded columns calculate on their own. The formulas, as they read in the first data row (row 4) of the sheet:
| Column | Excel formula | What it does |
|---|---|---|
| Present | =COUNTIF(E4:AI4,"P")+COUNTIF(E4:AI4,"OD")+COUNTIF(E4:AI4,"HD")/2 | Counts full days present, including on-duty days, and adds half of each half day. |
| Absent | =COUNTIF(E4:AI4,"A")+COUNTIF(E4:AI4,"HD")/2 | Counts absences, and adds the other half of each half day. |
| Paid leave | =COUNTIF(E4:AI4,"L") | Counts approved paid leave. |
| Weekly off | =COUNTIF(E4:AI4,"WO") | Counts weekly offs. |
| Holidays | =COUNTIF(E4:AI4,"H") | Counts paid holidays. |
| Worked offs | =COUNTIF(E4:AI4,"WP") | Counts weekly offs and holidays worked. Kept out of Present, because the day was not one the person was required to work. |
| Days marked | =AJ4+AK4+AL4+AM4+AN4+AO4 | The check. It should equal the days in the month unless the person joined or left during it. |
| Paid days | =AJ4+AL4+AM4+AN4+AO4 | Days to be paid for, worked offs included. This is the figure the salary sheet takes. |
| LOP days | =AK4 | Loss of pay days, for the deduction in the salary sheet. |
Daily log
The log behind the Weekly summary, for the week from Sunday 4 to Saturday 10 October 2026, with normal hours set to 8. Vikram Rao worked six hours on his weekly off and 8.5 hours on each of the next six days. Fatima Sheikh was on the night shift, and Arjun Nair was absent on 8 October.
| Date | Employee code | Employee name | Shift | In time | Out time | Break (minutes) | Hours worked | Spread-over | Spread check | Daily overtime | Week |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 04-10-2026 | E106 | Vikram Rao | General | 09:00 | 15:30 | 30 | 6 | 6.5 | 0 | 41 | |
| 05-10-2026 | E106 | Vikram Rao | General | 09:00 | 18:00 | 30 | 8.5 | 9 | 0.5 | 41 | |
| 06-10-2026 | E106 | Vikram Rao | General | 09:00 | 18:00 | 30 | 8.5 | 9 | 0.5 | 41 | |
| 07-10-2026 | E106 | Vikram Rao | General | 09:00 | 18:00 | 30 | 8.5 | 9 | 0.5 | 41 | |
| 08-10-2026 | E106 | Vikram Rao | General | 09:00 | 18:00 | 30 | 8.5 | 9 | 0.5 | 41 | |
| 09-10-2026 | E106 | Vikram Rao | General | 09:00 | 18:00 | 30 | 8.5 | 9 | 0.5 | 41 | |
| 10-10-2026 | E106 | Vikram Rao | General | 09:00 | 18:00 | 30 | 8.5 | 9 | 0.5 | 41 | |
| 05-10-2026 | E103 | Fatima Sheikh | Night | 22:00 | 06:30 | 30 | 8 | 8.5 | 0 | 41 | |
| 06-10-2026 | E103 | Fatima Sheikh | Night | 22:00 | 06:30 | 30 | 8 | 8.5 | 0 | 41 | |
| 07-10-2026 | E103 | Fatima Sheikh | Night | 22:00 | 06:30 | 30 | 8 | 8.5 | 0 | 41 | |
| 08-10-2026 | E103 | Fatima Sheikh | Night | 22:00 | 06:30 | 30 | 8 | 8.5 | 0 | 41 | |
| 09-10-2026 | E103 | Fatima Sheikh | Night | 22:00 | 06:30 | 30 | 8 | 8.5 | 0 | 41 | |
| 10-10-2026 | E103 | Fatima Sheikh | Night | 22:00 | 06:30 | 30 | 8 | 8.5 | 0 | 41 | |
| 05-10-2026 | E102 | Arjun Nair | General | 09:30 | 18:30 | 60 | 8 | 9 | 0 | 41 | |
| 06-10-2026 | E102 | Arjun Nair | General | 09:30 | 18:30 | 60 | 8 | 9 | 0 | 41 | |
| 07-10-2026 | E102 | Arjun Nair | General | 09:30 | 18:30 | 60 | 8 | 9 | 0 | 41 | |
| 09-10-2026 | E102 | Arjun Nair | General | 09:30 | 18:30 | 60 | 8 | 9 | 0 | 41 | |
| 10-10-2026 | E102 | Arjun Nair | General | 09:30 | 18:30 | 60 | 8 | 9 | 0 | 41 |
- Hours worked are in decimal hours: 8.25 is eight hours and fifteen minutes.
- A shift that runs past midnight needs no workaround: MOD turns 22:00 to 06:30 into 8.5 hours. Date the row by the day the shift started, because hours after midnight count in the previous day under section 28 of the OSH Code.
- Spread-over runs from in time to out time with breaks included. The flag marks a day over 12 hours.
- The week runs from Sunday, WEEKNUM(date,1), because the OSH Code's week begins at midnight on Saturday night.
Shaded columns calculate on their own. The formulas, as they read in the first data row (row 4) of the sheet:
| Column | Excel formula | What it does |
|---|---|---|
| Hours worked | =ROUND(MOD(F4-E4,1)*24-G4/60,2) | Out time less in time, converted to hours, less the unpaid break. MOD keeps a shift that crosses midnight positive. |
| Spread-over | =ROUND(MOD(F4-E4,1)*24,2) | Elapsed hours from in to out, breaks included. |
| Spread check | =IF(I4>12,"Over 12","") | Flags a day whose spread-over is above the 12 hours the Central Rules allow. |
| Daily overtime | =ROUND(MAX(0,H4-Settings!$B$2),2) | Hours worked above the normal hours set on the Settings sheet, and zero otherwise. |
| Week | =WEEKNUM(A4,1) | Week number, with weeks starting on Sunday. The Weekly summary adds up hours by employee and week. |
Weekly summary
The same week. Vikram Rao's daily overtime adds up to 3 hours, but his week totals 57 hours, 9 above 48, so 9 hours are payable at twice the rate, not 3. He also worked seven days in the week, which section 26 of the OSH Code allows only under an exemption, with a compensatory holiday to follow.
| Employee code | Employee name | Week | Hours in week | Daily overtime | Weekly overtime | Overtime payable |
|---|---|---|---|---|---|---|
| E106 | Vikram Rao | 41 | 57 | 3 | 9 | 9 |
| E103 | Fatima Sheikh | 41 | 48 | 0 | 0 | 0 |
| E102 | Arjun Nair | 41 | 40 | 0 | 0 | 0 |
- Overtime is paid at twice the rate of wages under section 27 of the OSH Code, calculated on the daily or the weekly basis, whichever is more favourable to the worker. This sheet counts the hours; payroll works out the wage.
- Apply the prescribed rounding of part-hours, which runs in the worker's favour, to the Overtime payable figure.
Shaded columns calculate on their own. The formulas, as they read in the first data row (row 4) of the sheet:
| Column | Excel formula | What it does |
|---|---|---|
| Hours in week | =SUMIFS('Daily log'!$H$4:$H$81,'Daily log'!$B$4:$B$81,A4,'Daily log'!$L$4:$L$81,C4) | Hours worked by this employee in this week, from the Daily log. |
| Daily overtime | =SUMIFS('Daily log'!$K$4:$K$81,'Daily log'!$B$4:$B$81,A4,'Daily log'!$L$4:$L$81,C4) | The daily test: overtime hours above the normal day, added up for the week. |
| Weekly overtime | =ROUND(MAX(0,D4-Settings!$B$3),2) | The weekly test: hours in the week above the weekly limit on the Settings sheet. |
| Overtime payable | =MAX(E4,F4) | The larger of the two tests, which is the one more favourable to the worker. |
What it has to contain
| Element | Why it matters |
|---|---|
| The month and year on the sheet itself | Attendance sheets are saved, copied and emailed. A file called attendance-final.xlsx with no month in it cannot be matched to a payroll run six months later, which is when someone asks. |
| An employee code that matches payroll | Names repeat and get spelled two ways. The code is the key the salary sheet looks up, and a mismatch is how one person gets paid for another's days. |
| A fixed list of codes, enforced | COUNTIF counts exactly what it is told to. "p", "P " and "Present" are three different values, and two of them vanish from the total. The template's dropdowns accept only the eight codes. |
| Weekly offs and holidays marked explicitly | If the weekly off is left blank, a blank stops meaning anything. Marking WO and H lets the check column catch the cell someone forgot to fill. |
| A half day code that splits the day | A half day marked P overpays, and marked A underpays. Counting HD as half in each column is the only treatment that keeps present plus absent equal to the working days. |
| Paid days and LOP days as separate totals | Payroll needs both. Paid days drive the proration of salary, and LOP days drive the deduction, and the two are not interchangeable when someone joins or leaves mid-month. |
How to use it
- Set up the month. Copy the Monthly attendance sheet, change the title to the new month, and clear the grid. For a month with fewer than 31 days, leave the extra day columns empty for everyone.
- Mark weekly offs and holidays first. Before the month starts, fill WO for every weekly off and H for every paid holiday across all rows. What is left blank is then exactly the working days still to be marked.
- Mark each working day from the source record. Fill P, A, L, HD or OD each day, and WP over any weekly off or holiday that was worked, from whatever is your source of truth: a biometric export, a login record or the supervisor's register. Use the dropdown so only valid codes go in.
- Reconcile leave before closing. Check every L against an approved leave request, and every A against the leave balance. An absence covered by approved leave is L, not A, and the difference is a day's pay.
- Close the month and check the totals. On the cut-off date, confirm that Days marked equals the days in the month for everyone who was employed throughout. Investigate any row that does not before payroll takes the figures.
- Hand paid days and LOP days to payroll. Copy the Paid days and LOP days columns into the salary sheet against each employee code, then lock or save a read-only copy of the attendance sheet for the record.
Paid days and LOP days: the two numbers payroll needs
Paid days are the days an employee is paid for: days present, approved paid leave, weekly offs and paid holidays, including any that were worked. Loss of pay days are the days absent without approved leave, including the absent half of each half day. Salary is prorated on paid days, and the LOP deduction follows from the absent days.
Most attendance disputes are not about whether someone came in. They are about how a day was counted. The sheet settles that by fixing the rule once, in the formulas, rather than leaving it to whoever does payroll that month.
Take Arjun Nair in the example. He was absent on two days and took a half day on a third. His present count is 23.5, his absent count is 2.5, and with four weekly offs and one holiday his paid days come to 28.5 out of 31. Payroll pays him 28.5 thirty-firsts of his monthly gross. Nothing else needs to be calculated by hand.
Rahul Verma joined on 12 October. His first eleven days are blank, because he was not yet employed, so they are neither paid nor loss of pay. His paid days are 19.5. The salary sheet prorates his pay on that figure, and his single half day of absence is the only loss of pay. Marking his pre-joining days A would have deducted them a second time.
The loss of pay rule itself is a policy decision, and the place to record it is the attendance policy. The sheet only applies it.
Monthly sheet or daily log
Use the monthly sheet where pay depends on whether someone was present, which covers most salaried staff. It answers one question per person per day and produces paid days at month end.
Use the daily log where pay depends on hours. That means shift work, overtime, or any arrangement where a late arrival or an early exit has a consequence. The log records in time, out time and break, and works out hours worked, the spread-over and the hours above the normal day. The Weekly summary then adds each person's week and compares the daily overtime with the hours above 48, because overtime is due on whichever basis is more favourable to the worker. A week with a worked weekly off is where the two differ. The normal day and the weekly limit are settings, so each can be changed in one place.
Many employers keep both: the daily log as the working record on the floor, and the monthly sheet as the summary payroll reads. In that case the monthly code for each day should follow from the log, so the two never tell different stories.
Where a spreadsheet stops working
A spreadsheet works while one person keeps it, the headcount is small, and attendance comes from one place. It stops working when attendance is captured in several places, when regularisation requests arrive by message, or when the sheet is shared and edited by several managers at once.
The failure is rarely the formulas. It is that the spreadsheet becomes a copy of other records that are each changing on their own. A biometric device has one version of the day, the leave approvals sit in email, a field employee's location is in a chat group, and the sheet is retyped from all three at month end.
Each retyping is a chance to get a day wrong, and there is no trail of who changed what. When an employee disputes their LOP, the sheet cannot show when the cell was changed, by whom, or on what evidence.
That is the point at which an attendance system earns its cost. It captures the punch, applies the leave and regularisation rules, and hands payroll the paid days directly, with a record of every change.
Common mistakes
| Mistake | Why it causes trouble | What to do instead |
|---|---|---|
| Leaving weekly offs blank | A blank then means both "weekly off" and "forgot to mark", and nobody can tell which. Paid days comes out short for anyone with a forgotten cell, and the error is invisible. | Fill WO and H for the whole month before it starts, so a blank can only mean an unmarked day. |
| Typing codes by hand | Lower case, trailing spaces and words like "leave" fall outside every COUNTIF, so the totals silently drop days. | Use the dropdown lists in the template, which reject anything outside the eight codes. |
| Marking a half day as P or A | Either the employee is paid for a full day they did not work or loses a full day they half worked. Over a year of half days the difference is a visible grievance. | Use HD, which counts half present and half absent. |
| Treating days before joining as loss of pay | A new joiner's pre-joining days marked A show up as LOP, and the deduction is taken on top of the proration, so they are underpaid twice. | Leave days before joining and after leaving blank, and prorate salary on paid days against the days in the month. |
| Editing last month's sheet after payroll has run | The attendance record and the payslip now disagree, and nothing records why. The correction also never reaches the employee's pay. | Leave the closed sheet as it was and carry the correction into next month as an arrears or recovery line. |
Statutory reference
- Act
- Occupational Safety, Health and Working Conditions Code, 2020
- Key limits
- Section 33 leaves the form of the register to the appropriate Government. This template is a working record of attendance and hours. The statutory register follows the form notified for your state.
| Provision | What it says |
|---|---|
| Section 33 | Requires the employer to maintain a register, in the form prescribed by the appropriate Government and electronically or otherwise, covering work performed, normal working hours, the rest day in each period of seven days, wages paid, leave, leave wages, overtime and attendance, among other particulars. |
Read the Occupational Safety, Health and Working Conditions Code, 2020 in full
Frequently asked questions
How do I make an attendance sheet in Excel?
List employees down the rows with their code and name, put the days of the month across the columns, and mark each cell with one code: P, A, L, HD, WO, H, OD or WP. Then add COUNTIF totals for each code and two result columns, paid days and loss of pay days. The free template on this page has all of that built in, with dropdowns and formulas.
What is the formula to count present days in an attendance sheet?
=COUNTIF(E4:AI4,"P")+COUNTIF(E4:AI4,"OD")+COUNTIF(E4:AI4,"HD")/2 counts the full days present in row 4, including days on duty away from the workplace, and adds half of each half day. Change the range to match your day columns. The same pattern with "A" counts absences, and with "L" counts paid leave.
How are paid days calculated from attendance?
Paid days are days present plus approved paid leave plus weekly offs plus paid holidays, with any weekly off or holiday that was worked counted once. Absences without approved leave, and the absent half of each half day, are loss of pay days. Salary for the month is the monthly gross multiplied by paid days and divided by the days in the month.
Should weekly offs be counted as paid days?
Yes, for a monthly-paid employee the weekly off is part of the month they are paid for, which is why the template counts WO in paid days. The exception is a new joiner or leaver, whose weekly offs count only within the days they were on the rolls.
How should a half day be marked?
With its own code, HD, which the template counts as half a day present and half a day absent. Marking it P overpays and marking it A underpays, and either way the month stops adding up.
Is an attendance sheet the same as the statutory attendance register?
No. The statutory register is kept in the form prescribed by the appropriate Government and carries more than attendance: hours, rest days, leave, overtime and other particulars. A monthly attendance sheet is the working record you fill that register from. Check your state's notified form for the exact columns.
Can I use this attendance sheet for a month with 28, 29 or 30 days?
Yes. Leave the day columns beyond the last date of the month empty for everyone. The totals only count codes, so empty columns change nothing, and the Days marked check then equals the days in that month.
How long should attendance records be kept?
Keep them at least as long as the payroll records they support, and for as long as your state's rules under the labour codes require for the prescribed registers. A dispute about pay often arrives a year or more after the month in question, and the attendance record is the evidence.
Attendance without the spreadsheet
Engage captures attendance from biometric devices, mobile check-ins with geofencing, and web punches, applies your leave, half-day and regularisation rules, and hands payroll the paid days and LOP days directly. Every change to a day is logged with who made it and why, so a disputed LOP can be answered from the record.
See attendance management