What this attendance-and-salary sheet is for
Most small companies keep attendance in one sheet and salary in another, and the monthly hand-off between them, typing paid days from one into the other, is where mistakes happen. An attendance sheet with salary calculation puts the day codes and the rupee result in the same row, so a change to one day's mark changes the earned salary immediately and visibly.
This template is for owners and accountants of firms with roughly 5 to 100 monthly-salaried employees where salary is a fixed gross prorated for absence, with overtime paid on hours. It stops before statutory deductions on purpose: the output is Net Before Deductions, which you carry into the salary sheet with PF and ESI formulas or your payroll software. Keeping the two steps separate makes each easy to audit.
The one decision that changes everyone's pay is the per-day divisor, and the template makes it a single Settings cell so you can see the effect of calendar days, 26 or 30 before you commit. The salary per day and LOP guide explains when each is appropriate.
What is inside: Attendance & Salary and Settings
The Attendance & Salary sheet has a header with Company and Month, and one row per employee: Emp ID, Name, Monthly Gross, then Days 1 to 31 with codes P (present), HD (half day), PL (paid leave), WO (weekly off), H (holiday) and A (absent, unpaid). After the day grid come the counts: Present, Half Day, Paid Leave, WO, Holiday; then Payable Days, Per Day Rate, Earned Salary, LOP Amount, OT Hours, OT Amount and Net Before Deductions.
Payable Days is Present plus half of Half Day plus Paid Leave plus WO plus Holiday, capped at Days in Month. Per Day Rate is Monthly Gross divided by the divisor chosen on the Settings sheet. Earned Salary is Per Day Rate times Payable Days, and LOP Amount is Monthly Gross minus Earned Salary, which is the figure employees ask about. OT Amount is OT Hours times the hourly rate (Per Day Rate divided by 8) times the OT multiplier from Settings.
The Settings sheet has: Days in Month (typed, 28 to 31), Divisor Mode with a dropdown of Calendar Days, 26 or 30, OT Multiplier (default 2) and Normal Hours per Day (default 8). A note under Divisor Mode records which mode you used for the month so the choice is documented if anyone asks later.
- Present: =COUNTIF(D5:AH5,"P"); Half Day: =COUNTIF(D5:AH5,"HD"); and so on for PL, WO and H.
- Payable Days: =MIN(Present + 0.5×HalfDay + PaidLeave + WO + Holiday, Settings!DaysInMonth).
- Per Day Rate: =Monthly Gross ÷ IF(Settings!Mode="26",26,IF(Settings!Mode="30",30,Settings!DaysInMonth)).
- Earned Salary: =Per Day Rate × Payable Days; LOP Amount: =Monthly Gross − Earned Salary.
- OT Amount: =OT Hours × (Per Day Rate ÷ Settings!NormalHours) × Settings!OTMultiplier.
How to use it each month
Copy last month's file, set Days in Month and confirm Divisor Mode on Settings, and update Monthly Gross for anyone with an increment. Mark the day codes as the month goes, ideally daily from your register or app export; the monthly attendance sheet uses the same codes if you prefer to mark there and paste the row in.
Mark WO on the actual weekly off days and H on declared holidays for every employee, because both are paid days and an unmarked WO becomes an unpaid A. Enter OT Hours from your overtime record at month end. Earned Salary, LOP Amount and Net Before Deductions update as you type.
Before finalising, sort by LOP Amount descending and look at the top few rows; an employee with ₹8,000 LOP usually has a marking error or an unrecorded leave. Then copy Emp ID, Payable Days, Earned Salary and OT Amount into your salary sheet for PF, ESI, PT and TDS, or check individual rows against the salary per day calculator.
- Data validation on the day cells limited to P, HD, PL, WO, H and A keeps the COUNTIFs honest.
- Freeze panes after column C so names stay visible across 31 days.
- Save one file per month as your attendance-cum-wage working; do not overwrite.
How to customise the codes and formulas
Add codes for situations your company recognises: OD for on-duty outside the office (paid), CO for a compensatory off taken (paid), and LWP for approved leave without pay (unpaid, like A but distinguishable). Extend Payable Days to add the paid codes. If you pay half-day leave, add 0.5 times a HDL code in the same way.
If your policy applies the 26-day divisor only to workers on minimum wages and calendar days to office staff, add a per-employee Divisor column that defaults to the Settings mode but can be overridden, and point Per Day Rate to it. If overtime is paid at single rate for some categories by agreement, add a per-employee multiplier column, but remember the statutory rate for eligible workers is double.
For late-mark deductions, add a Late count column and a Settings rule such as three late marks equal one half day, then subtract the resulting half days from Payable Days. Make sure the rule is written in your attendance policy before you deduct anything.
Rules the sheet must respect
Weekly offs and paid holidays are paid days for monthly-salaried employees, so they count in Payable Days; deducting for a weekly off is one of the most common errors in hand-made sheets. Earned leave, casual leave and sick leave taken within entitlement are paid; only absence beyond entitlement or unapproved absence is loss of pay. The loss of pay glossary entry defines the term and how it interacts with leave balances.
The divisor choice affects the per-day rate and therefore every LOP and overtime figure. Dividing by 26 gives a higher per-day rate than dividing by 30 or 31 and is the common practice for daily-rated and minimum-wage workers under state notifications; monthly-salaried companies typically use calendar days or 30. Whatever you choose, apply it consistently, write it into the appointment letter or policy, and use the same basis for encashment and notice-period recovery.
Overtime beyond normal daily hours or 48 hours a week is payable at not less than twice the ordinary rate under the Factories Act and the Code on Wages, and needs the employee's consent under the Labour Codes. The hourly rate should be computed on wages as defined, so if your Monthly Gross includes allowances beyond 50% of pay, those are added back for the OT base.
When the sheet stops working
The combined sheet holds up while one person marks attendance and the codes are trusted. It fails when attendance comes from several sources (a biometric machine, a WhatsApp group for field staff, a supervisor's register) and someone has to reconcile them into codes; when employees dispute an A and there is no evidence either way; and when the Payable Days figure still has to be typed into a separate payroll tool each month.
Attendance-linked payroll removes the typing and the argument. In Attend Mitra, attendance is captured by face, GPS, QR or biometric device, late marks and half days follow rules set per shift, approved leave and holidays are already paid days, and the payroll run derives LOP and overtime directly, applies EPF, ESIC, PT and TDS settings and produces payslips and a bank file. The salary calculation attendance software page shows the same Payable Days to Net Pay logic this template uses, applied automatically.