What this in-out time attendance sheet is for
A status-only sheet tells you who came; this one tells you when they came, when they left, and how long they actually worked. It is meant for businesses where hours drive pay or discipline: a restaurant paying kitchen staff for extra hours, a clinic with staggered shifts, a small factory that must track a 9-hour day under the Factories Act, or an office with a late-coming policy that deducts after three late marks.
The design is a daily log rather than a grid. Every row is one employee on one date. That structure is what lets Excel calculate hours with simple formulas and then summarise by employee with SUMIF. It is also the shape that payroll software and the working hours calculator expect if you later need to import or check the data.
It works for up to a few hundred rows a month (say 20 employees x 26 days = 520 rows) without slowing down. Above that, filtering and manual entry become the bottleneck rather than Excel itself.
What is inside: the Daily Log and Summary sheets
Sheet one is Daily Log. Its columns, left to right, are Date, Emp ID, Name, Shift Start, Shift End, In Time, Out Time, Break (mins), Worked Hours, Late By (mins), Overtime Hours and Remarks. Shift Start and Shift End are the scheduled times for that employee that day (for example 09:30 and 18:30). In Time and Out Time are what actually happened. Break is entered in minutes because most Indian workplaces give a fixed 30 or 45-minute lunch and it is easier to type 30 than 00:30.
Worked Hours is calculated: =IF(OR(In="",Out=""),"",MOD(Out-In,1)*24-Break/60). The MOD(...,1) wrapper handles shifts that cross midnight, so an In of 21:00 and an Out of 06:00 correctly returns 9 hours rather than a negative number. Multiplying by 24 converts Excel's fractional-day time into decimal hours, and the IF guard keeps the cell blank until both punches exist. Late By is =IF(In>ShiftStart,(In-ShiftStart)*1440,0), giving minutes. Overtime Hours is worked hours beyond the scheduled shift length: =MAX(0,WorkedHours-(MOD(ShiftEnd-ShiftStart,1)*24-Break/60)).
Sheet two is Summary. It has one row per employee with Emp ID, Name, Days Logged, Total Worked Hours, Total Late Minutes, Late Count and Total Overtime Hours. Each uses SUMIF or COUNTIF against the Daily Log, for example =SUMIF(DailyLog!B:B,A5,DailyLog!I:I) for total worked hours where column B holds Emp ID and column I holds Worked Hours. Late Count is =COUNTIFS(DailyLog!B:B,A5,DailyLog!J:J,">0").
- All time cells are formatted hh:mm so you can type 9:35 and Excel stores it correctly
- Worked Hours and Overtime Hours are decimal (8.50 not 8:30) because payroll multiplies them by a rate
- Remarks is free text for OD, half day, missed punch and similar notes
How to use it day by day
Enter one row per employee per working day. The fastest method is to pre-fill the Date, Emp ID, Name, Shift Start and Shift End for the whole month on day one (26 rows per employee), sort by date, and then only type In, Out and Break as the month progresses. Pre-filling also makes missed punches visible: any row with a Date in the past and a blank In Time is a missed punch to chase.
For a missed out-punch, do not guess the time. Put the shift end time in Out Time and write MP in Remarks, or leave it blank so Worked Hours stays empty and payroll treats it under your missed-punch rule. The guide on handling missed punches in payroll explains the common rules.
At month end, the Summary sheet is ready without any extra work. Check that Days Logged matches expected working days for each employee, then read Total Overtime Hours into your overtime sheet and Late Count into your late-coming policy. Print the Summary for signatures; the Daily Log is usually kept as the backup file.
- Turn on the filter on the Daily Log header row to view a single employee or a single date
- Sort by Late By descending on the last working day to see the month's habitual late-comers
- Use Ctrl+Shift+; to enter the current time when marking a live punch at a reception desk
How to customise grace, shifts and rounding
Almost every company allows a grace period. To add a 10-minute grace, change Late By to =IF(In>ShiftStart+TIME(0,10,0),(In-ShiftStart)*1440,0). The late mark and grace period entries in the glossary cover how companies usually set these, including the three-lates-equals-half-day rule that many Indian offices use.
If your team has fixed shifts, put the shift times in a small lookup table on the Summary sheet and use VLOOKUP on a Shift Code column so that typing G, M or N fills Shift Start and Shift End automatically. For rotating rosters, the shift code will differ by date, so keep the manual Shift Start and Shift End columns and fill them from your roster.
For overtime rounding, wrap the Overtime Hours formula in FLOOR(...,0.5) to pay in half-hour blocks, or ROUND(...,2) for exact minutes. Check your state's rules and any settlement with workers before rounding down; the safe default is exact minutes. For payroll, the Excel hours-for-payroll guide shows how the Summary feeds a salary sheet.
- Add an Early Exit (mins) column with =IF(AND(Out<>"",Out<ShiftEnd),(ShiftEnd-Out)*1440,0)
- Add a Status column: =IF(WorkedHours="","",IF(WorkedHours<4,"HD","P")) to derive half days from hours
- Change 1440 to 60 if you prefer late minutes shown as decimal hours
Rules and compliance context
Recording actual in and out times is what makes daily-hours limits checkable. The Factories Act 1948 caps work at 9 hours a day and 48 a week with a spread-over of 10.5 hours, and requires overtime at twice the ordinary rate beyond those limits. Most state Shops and Establishments Acts apply similar daily and weekly caps to shops and offices. The Worked Hours and Overtime Hours columns give you the evidence either way, and the overtime calculation formula explains how to convert overtime hours into rupees.
Under the Labour Codes in force since 21 November 2025, overtime must be paid at not less than twice the normal wage and requires the worker's consent. Keeping a per-day record with an Out Time later than Shift End is your consent-and-payment trail; a status-only sheet cannot show this.
In and out times are personal data about an identifiable employee. Under the DPDP Act 2023 you should tell employees what is recorded and why, keep it only as long as needed for payroll and compliance, and restrict who can open the file. A shared workbook on a common desktop rarely meets that standard.
When the spreadsheet stops working
The weak point of this sheet is not the formulas but the source of the times. Someone has to type them, either from a paper sign-in book, a biometric device export or from memory. Typing 500 rows a month is an afternoon of work and every row is a chance for a transposed digit. The other weak point is trust: an employee disputing a late mark has no way to check what was recorded, and an admin can quietly edit a cell.
Attend Mitra captures the punch in and punch out directly from the employee's phone with face or selfie verification and site geofence, applies the shift's grace and break rules, and produces this same daily log and summary as an export. Late marks, early exits and overtime appear per day, corrections need approval and leave an audit trail, and offline punches sync when the network returns. See the attendance solution for how the shift rules are configured, or keep using this sheet and export from a biometric device into it using the biometric-to-payroll guide.