What this overtime sheet is for
Overtime is the most disputed line on an Indian payslip and the one an inspector checks first, because the law fixes the rate (twice the ordinary wage) and caps the hours. This template is an overtime register: one row per employee per day on which overtime was worked, with the hours calculated from shift end and actual out time, the rate applied, and the approval recorded. It replaces the loose slips, WhatsApp messages and supervisor memory that overtime usually runs on.
It is meant for factories, workshops, warehouses, hotels, hospitals, security agencies and any business where staff regularly work beyond their shift and are paid for it. It works alongside an attendance sheet that records times (the attendance sheet with in and out time is the natural pair) and feeds the OT amount into the salary sheet.
Who uses it: the supervisor who authorises the extra hours, the timekeeper or HR executive who records them, and the payroll executive who converts them into pay. The Approved By column exists so that all three can see who said yes.
What is inside: OT Register and Monthly Summary sheets
The OT Register sheet has these columns: Date, Emp ID, Name, Dept, Shift End (scheduled), Actual Out, OT Hours, OT Rate Multiplier, Hourly Wage, OT Amount and Approved By. Shift End and Actual Out are entered as times. OT Hours is calculated: =IF(OR(E6="",F6=""),"",MAX(0,MOD(F6-E6,1)*24)), which returns decimal hours beyond shift end and correctly handles a shift end of 22:00 with an actual out of 01:30 the next morning.
OT Rate Multiplier defaults to 2, the statutory double rate under the Factories Act and the Code on Wages. Hourly Wage is the employee's ordinary hourly rate, normally (monthly wage / 26) / 8 for daily-rated and minimum-wage workers. The template includes a helper column where you enter the monthly wage and the hourly rate is derived with =ROUND(H6/26/8,2). OT Amount is =G6*I6*J6, that is OT Hours x Hourly Wage x Multiplier. Approved By is a name or initials; the row is not payable without it.
The Monthly Summary sheet lists each employee once with Emp ID, Name, Dept, Total OT Hours (=SUMIF(Register!B:B,A5,Register!G:G)), Total OT Amount (same pattern on the OT Amount column), OT Instances (=COUNTIF(Register!B:B,A5)) and a Cap Check column. Cap Check compares Total OT Hours with a monthly limit you set in a cell at the top (the template ships with a blank for you to fill from your state rules) and shows OVER when exceeded, with a conditional format in red.
- OT Hours is decimal (1.50 not 1:30) so it multiplies cleanly with the rate
- Hourly Wage is stored per row so a mid-month wage revision does not rewrite past rows
- Approved By is a text cell; use Data Validation with a list of authorised supervisors to prevent self-approval
How to use it: recording, approving and paying overtime
Record overtime the same day it is worked. The supervisor tells the timekeeper the employee stayed till 21:45 against a 18:00 shift end; the timekeeper enters the row, the formula shows 3.75 OT hours, and the supervisor's name goes in Approved By. If the extra work was on a weekly off or holiday (a full extra duty rather than an extension), enter Shift End as the normal start time and Actual Out as the end so the full shift is counted, and mark the date in a Remarks note if you add one.
Enter the hourly wage from your wage master. For a worker on a monthly wage of ₹15,600, the hourly rate is 15,600 / 26 / 8 = ₹75, and 3.75 hours at double rate is 3.75 x 75 x 2 = ₹562.50. The overtime pay calculator does the same arithmetic if you want to check a figure quickly.
Before payroll, open the Monthly Summary. Check every OVER flag and every employee with unusually high OT instances; both are signs of chronic understaffing that overtime is masking. Then copy Total OT Amount into the OT column of your salary sheet. Print the Register for the month and have the department heads sign the foot of the page; that is your overtime register for inspection.
- Sort the Register by Emp ID at month end to see each person's OT pattern
- Filter Approved By for blanks before payroll; unapproved rows should not be paid until resolved
- Keep one Register file per month; the Monthly Summary refers to the same file
How to customise rounding, rates and thresholds
Many companies round overtime to the nearest 15 or 30 minutes. Wrap the OT Hours formula in MROUND(...,0.25) for quarter hours or FLOOR(...,0.5) to pay only in completed half hours. Rounding down is legally risky for minimum-wage workers because it can push effective pay below the notified rate; if you round, round to the nearest, not down.
If your policy pays single rate for the first hour of extension and double thereafter, split OT Hours into two columns with =MIN(G6,1) and =MAX(0,G6-1) and apply separate multipliers. Under the Factories Act and the Code on Wages, however, all hours beyond the daily or weekly limit are at double rate, so this split is only appropriate for hours that are beyond the shift but still within the legal daily limit (for example a 7-hour shift extended to 9).
For salaried staff who are paid overtime, the hourly rate divisor may be 30 days and 8 hours rather than 26 and 8; change the helper formula accordingly and record the divisor on the Summary sheet. Add a Weekly Hours check by combining this register with the attendance sheet if your state caps weekly overtime as well as monthly or quarterly totals.
- Add a Reason column (breakdown, order rush, absentee cover) to analyse why overtime is happening
- Add a Comp-Off Instead (Y/N) column if your policy allows time off in lieu for some categories
- Protect the formula columns and leave only Date, IDs, times, wage and Approved By editable
Rules and compliance context
Under section 59 of the Factories Act 1948, a worker who works more than 9 hours in a day or 48 in a week is entitled to wages at twice the ordinary rate for the overtime. The Code on Wages 2019, in force since 21 November 2025, applies the same double rate across all establishments it covers, and the Labour Codes also make overtime consent-based. Most state Shops and Establishments Acts already required double rate for shops and offices. The 2x default in this template is therefore the legal floor, not a company choice.
The ordinary rate for overtime includes basic pay and dearness allowance and, under the Factories Act, the cash value of certain concessions; it excludes bonus and overtime itself. For minimum-wage workers the ordinary rate can never be below the notified minimum wage for the category, so the wage master must be updated when the state revises VDA (typically April and October). The overtime glossary entry summarises the definitions.
Overtime hours are capped by the Factories Act and state rules per quarter, and the Labour Codes leave the limits to be notified by the appropriate government; state figures differ, so the template leaves the Cap Check limit blank for you to fill from your state's current rules. An overtime register in the prescribed form (Form for register of overtime under the Factories rules, or the register under the Contract Labour rules for contractors) must be maintained and produced on inspection; this sheet, printed and signed monthly, is a practical equivalent, but check whether your state requires its specific form.
When the overtime sheet stops working
The register depends on somebody knowing the actual out time. Where attendance is on paper, the supervisor's memory is the source; where it is on a biometric device, someone exports the punches, matches them to shift ends and types the extension into this sheet. Both are slow and both are where disputes start: the employee says 21:45, the sheet says 21:00, and there is no timestamp either can point to.
Attend Mitra derives overtime from the actual punch against the shift template. The shift's end time, grace and break rules are configured once; each day's punch out beyond the end creates overtime hours automatically, with the approval flow and the hourly rate from the employee's wage master applied in the payroll run. The OT register and the monthly summary are exports rather than data entry, and the same overtime feeds the payslip. The automatic overtime calculation software page shows the setup; the overtime from timesheets guide covers the manual route if you are staying with Excel for now.