Download a free monthly attendance sheet in Excel with formula, made for Indian offices, shops and factories. Pick a code for each day and the sheet does the rest: dates and weekdays fill in for every month, Sundays, Saturdays (or 2nd and 4th Saturdays) and holidays are shaded, and each employee's present days, leave, LOP days, paid days, attendance % and salary after LOP are worked out. There are 12 month sheets for FY 2026-27, a year summary and a CL/SL/EL leave balance, for up to 50 employees. No macros, no sign-up.
Download: monthly attendance sheet Excel (FY 2026-27, 12 months, LOP and paid days, leave balance)
Download Attendance Sheet (.xlsx)Preview in Google Drive
File: Monthly-Attendance-Sheet-Excel-2026-HRCalcy.xlsx (Excel, about 310 KB, no macros). Free, no sign-up, hosted on Google Drive. Works in Microsoft Excel, Google Sheets and LibreOffice. In Google Sheets, use File → Make a copy before editing. Need a quick figure for one person? Use the online attendance calculator.
What Is in the Attendance Sheet Excel
| Sheet | What it does |
|---|---|
| Instructions | How to fill the file, the codes and every formula used |
| Settings | Company name, financial year, weekly off 1 and 2, and the per-day salary basis (calendar days, 30 or 26) |
| Employees | Employee ID, name, department, designation, date of joining, date of leaving and monthly gross for up to 50 employees |
| Holidays | Up to 60 paid holidays; Independence Day, Gandhi Jayanti and Republic Day are already entered |
| Apr to Mar (12 sheets) | The day-wise attendance register for each month, with 20 summary columns per employee: P, OD, HD, CL, SL, EL, LWP, A, days on rolls, working days, present on working days, worked on off days, unmarked days, LOP days, paid days, attendance %, OT hours, monthly gross, LOP deduction and salary after LOP |
| Year Summary | Paid days for each month and yearly totals of present days, leave, LOP, overtime hours and LOP deduction |
| Leave Balance | CL, SL and EL: opening balance + credited − taken = balance |
How to Use It
- Settings: enter your company name and the year your financial year starts (2026 for FY 2026-27). Choose weekly off 1 and weekly off 2 (for example Sunday and Saturday, or Sunday and 2nd & 4th Saturday) and the per-day salary basis your payroll uses.
- Employees: replace the three example employees with your own. Add the date of joining and, if someone has left, the date of leaving, so days outside their employment are not paid.
- Holidays: add your State's festival and other paid holidays with their dates. Every month sheet picks them up and shades them orange.
- Month sheets: in each day cell pick a code from the dropdown. Leave weekly offs and holidays blank. Enter overtime hours in the OT column. Fill only yellow cells or the day grid; everything else is a formula.
- Check the Unmarked working days column. A red number means a working day has no entry yet.
Attendance Codes Used
| Code | Meaning | How it is counted |
|---|---|---|
| P | Present | Present, paid |
| HD | Half day | 0.5 present, 0.5 LOP |
| OD | On duty or tour | Present, paid |
| CL / SL / EL | Casual, sick, earned leave | Paid leave, deducted from the leave balance |
| LWP | Leave without pay | LOP |
| A | Absent (unauthorised) | LOP |
| WO / H | Weekly off / holiday | Paid; optional to mark, as the sheet shades these days itself |
A P on a weekly off or holiday is counted in Worked on off days, so you can see who needs a compensatory off or overtime for that day.
Formulas Used in the Attendance Sheet
These are the actual formulas in the file. You can use them to build your own sheet or to check ours. The day grid runs from column E (day 1) to column AI (day 31); row 6 holds dates, row 7 weekdays, row 8 the day type and row 9 onwards the employees.
| What | Formula (first cell) |
|---|---|
| First day of the month | =DATE(Settings!$B$5,4,1) (April; January to March add 1 to the year) |
| Days in the month | =DAY(EOMONTH($C$3,0)) |
| Date for day 1 to 31 | =IF(1<=$G$3,$C$3+0,""), then 2, 3 … so days 29 to 31 go blank in shorter months |
| Weekday name | =IF(E$6="","",TEXT(E$6,"ddd")) |
| Holiday, weekly off or working day | COUNTIF(Holidays!$A$5:$A$64,E$6)>0 gives "H"; WEEKDAY(E$6,2) matched with the weekly-off settings gives "WO"; otherwise "W" |
| 2nd and 4th Saturday | WEEKDAY(E$6,2)=6 and INT((DAY(E$6)-1)/7)+1 equal to 2 or 4 |
| Count of a code | =COUNTIF($E9:$AI9,"P") (same for OD, HD, CL, SL, EL, LWP, A) |
| Days on rolls | =SUMPRODUCT(ISNUMBER(dates)*(dates>=joining)*(dates<=leaving)) |
| LOP days | =A + LWP + 0.5*HD |
| Paid days | =MAX(0, days on rolls − LOP days) |
| LOP deduction | =ROUND(gross / basis * (LOP days + days not on rolls), 0) |
The sheet uses only standard functions (DATE, EOMONTH, TEXT, WEEKDAY, COUNTIF, SUMPRODUCT, ROUND), so it works the same in Excel 2010 onwards, Google Sheets and LibreOffice. Unlike many templates, the dates update by themselves when you change the year, and the counts adjust to 28, 29, 30 or 31 days.
Worked Example: April 2026 (Values in the Download)
Settings: Saturday and Sunday off, salary basis = calendar days. April 2026 has 30 days, 8 weekly offs and 22 working days.
| Item | Asha (Accounts) | Rohit (Sales) | Neha (joined 15 Apr) |
|---|---|---|---|
| Entries | 20 P, 1 CL, 1 HD | 20 P (one on a Saturday), 1 SL, 1 LWP, 1 A | 12 P |
| Days on rolls | 30 | 30 | 16 |
| LOP days | 0.5 | 2 | 0 |
| Paid days | 29.5 | 28 | 16 |
| Attendance % | 93.2% (20.5 of 22) | 86.4% (19 of 22) | 100% (12 of 12) |
| Monthly gross | ₹32,000 | ₹28,000 | ₹24,000 |
| LOP deduction | ₹533 (32,000 ÷ 30 × 0.5) | ₹1,867 (28,000 ÷ 30 × 2) | ₹11,200 (24,000 ÷ 30 × 14 days before joining) |
| Salary after LOP | ₹31,467 | ₹26,133 | ₹12,800 |
Rohit's Saturday shows in Worked on off days (1). If Settings is changed to Sunday plus 2nd & 4th Saturday and a 26-day basis, his LOP deduction becomes ₹2,154 (28,000 ÷ 26 × 2) and the 1st and 3rd Saturdays turn into working days.
Paid Days and LOP: Which Salary Basis to Choose
The per-day rate decides how much one LOP day costs. Payroll in India commonly uses one of three bases; pick the one in your appointment letter or salary policy.
| Basis | Per-day rate | 1 LOP day on ₹30,000 |
|---|---|---|
| Calendar days | Gross ÷ days in the month (28 to 31) | ₹968 to ₹1,071 |
| 30 days | Gross ÷ 30, every month | ₹1,000 |
| 26 days | Gross ÷ 26 (weekly offs excluded) | ₹1,154 |
Weekly offs and holidays are paid in all three, so a full month with no LOP always pays the full gross. For the payslip itself, use the free salary slip generator; for take-home after PF, ESI and tax, use the in-hand salary calculator.
Weekly Offs, Alternate Saturdays and Holidays
- Weekly off 1 and 2: any weekday or None. Factories and shops with a six-day week usually choose Sunday and None.
- 2nd & 4th Saturday or 1st & 3rd Saturday: choose it as weekly off 2. The sheet finds those Saturdays for each month.
- Holidays: each date in the Holidays sheet becomes "H" in the right month. A holiday on a weekly off is shown once, as a holiday.
- Sandwich rule: treating weekly offs between two leave days as leave is a company policy, not a law. The sheet does not apply it; mark those days A or LWP yourself if your policy requires it.
Attendance Register Rules in India (2026)
| Rule | What it says |
|---|---|
| Register to keep | Attendance Register-cum-Muster Roll in Form IX, along with the Employee Register (Form I) and the Wage Register (Form IV): Rule 51, Code on Wages (Central) Rules, 2026 (G.S.R. 343(E), 8 May 2026) |
| Paper or electronic | Either is allowed |
| How long to keep it | 5 years after the date of the last entry |
| Wage slip | Form V, on or before payment of wages (Rule 52); it can be electronic |
| Overtime | At not less than twice the normal rate of wages (Section 14, Code on Wages, 2019); record the hours in the OT column |
| Annual leave | Under the OSH Code, a worker who has worked 180 days or more in a calendar year earns 1 day of leave for every 20 days worked |
This Excel records what an attendance register needs: employee details, day-wise attendance for the month, days present and overtime hours. States can prescribe their own forms under their rules and Shops and Establishments Acts, so check your State's format before using it as your statutory register. For overtime pay, use the overtime calculator; for leave payout, use the leave encashment calculator.
Excel Sheet or Online Calculator?
| Use the Excel when | Use the online calculator when |
|---|---|
| You keep attendance for a team, every day, for the whole year | You need one employee's or student's attendance % or LOP quickly |
| You want a record to save, print or share with payroll | You are on your phone and need an answer now |
| You need paid days, leave balance and a year summary | You want to check the 75% rule or how many classes you can miss |
The online attendance calculator covers students (75% rule, subject-wise) and employees (LOP and paid days).
Frequently Asked Questions
Is this attendance sheet Excel free?
Yes. It is free to download and use, with no sign-up and no macros. It opens in Microsoft Excel, Google Sheets and LibreOffice.
How do I calculate attendance in Excel with a formula?
Count each code with COUNTIF, for example =COUNTIF(E9:AI9,"P") for present days. Then LOP days = A + LWP + 0.5 × HD, paid days = days on rolls − LOP days, and attendance % = days present on working days ÷ working days.
How do I make dates change automatically for each month?
Put the first day of the month in one cell, find the days in the month with =DAY(EOMONTH(start,0)), and fill each day column with =IF(n<=days, start+n−1, ""). The weekday comes from =TEXT(date,"ddd"). The download already does this for all 12 months.
How are Sundays, Saturdays and holidays handled?
Choose two weekly offs in Settings (any weekday, 2nd & 4th Saturday, or 1st & 3rd Saturday) and list holidays in the Holidays sheet. The sheet shades those days and counts them as paid; you leave them blank.
How is LOP calculated in this sheet?
LOP days = absent (A) + leave without pay (LWP) + half of half days (HD). LOP deduction = monthly gross ÷ salary basis (calendar days, 30 or 26) × LOP days, plus any days before joining or after leaving.
Does it work for employees who join or leave mid-month?
Yes. Enter the date of joining or leaving in the Employees sheet. Days outside employment are not counted as days on rolls, so paid days and salary are reduced automatically.
Can I track CL, SL and EL balance?
Yes. Enter the opening balance and leave credited in the Leave Balance sheet. Leave taken is added up from all 12 month sheets, and the balance updates by itself.
How many employees can it handle?
Up to 50 employees for each of the 12 months of the financial year. For a larger team, use one file per department or branch.
Which attendance register is required under the labour codes?
Rule 51 of the Code on Wages (Central) Rules, 2026 requires an Attendance Register-cum-Muster Roll in Form IX, kept on paper or electronically, and preserved for 5 years after the last entry. States may prescribe their own forms.
Will it work in Google Sheets?
Yes. Open the Drive link, choose File → Make a copy, and edit your copy. Dropdowns, colours and formulas all work.