Employee Attendance Spreadsheet: Build Your Own, Then Switch

If you are searching for an employee attendance template to download, you are usually better off building the employee attendance spreadsheet yourself from the right columns, rather than trusting a file whose formulas you do not know. A well-built sheet is genuinely enough for a small team in one location, as long as it records times to the minute and calculates lateness and hours with protected formulas. It stops working when you add branches or field staff, because the sheet can only record what someone types into it. This guide covers the main formats (register, monthly sheet, timetable, spreadsheet), the steps to build one in Excel, the formulas, and then the signs it has broken and a plan to move to an app.

TL;DR. We do not offer a template download, because any ready-made file needs adjusting to your hours and holidays. Build two sheets: a staff list, and a daily log with one row per employee per working day. Store clock-in and clock-out as real times, calculate late minutes against a fixed start time, and code approved leave separately from unexcused absence. Then build a monthly attendance summary with COUNTIFS. Switch to an app when you have several branches or field staff, or when month-end reconciliation eats a full working day.

Attendance formats: register, monthly sheet, timetable or spreadsheet

"Attendance template" can mean very different things. Pick the format before you look for a file:

Format	What it looks like	Good for	Weakness
Paper attendance register	A numbered book where staff sign in with the time	One small site, or an employer that requires a paper record	Easy to sign for a colleague; all maths is manual
Monthly attendance sheet	One page per month: names in rows, days in columns	Seeing who came and who did not at a glance	No times to the minute, so no lateness
Weekly or two-shift timetable	A grid per week, or per morning and evening shift	Rotating shifts	Columns multiply fast
Excel or Google Sheets log	A daily log with formulas	Lateness, hours and payroll	Depends on whoever types into it
Printable Word or PDF sheet	A blank sign-in sheet	A training course or a one-day event	Calculates nothing; must be re-typed into Excel

A rule of thumb: a manual sign-in sheet proves who attended. If you will deduct pay or calculate overtime, you need a log of actual times, which means a spreadsheet, not a grid.

The paper attendance register and its opening record

Many companies and schools still keep a paper attendance register, sometimes because a body requires it, sometimes because it is the simplest option at a site without a computer. To make the register a reliable reference, many organisations follow a simple administrative practice: an opening note on the first page and a closing note on the last.

We are not aware of a single mandatory legal wording for this note in the private sector, so treat the following as practical points rather than an official form:

1. The opening note gives the business or branch name, the opening date, the number of pre-numbered pages, and the name and signature of the person responsible.
2. Number every page in advance, never remove a page, and strike errors through with one line and a signature instead of erasing.
3. Columns on each page: date, name or ID, time in, signature, time out, signature, notes.
4. The closing note records the date of the last entry and the number of pages used, signed by the person responsible.

If you work at a school or government body that uses a teachers' attendance register, follow the approved form and cover from the authority you report to, because those bodies have their own rules. Either way, the register is only a data source; you will still need to move it into a spreadsheet to calculate lateness.

How to make an attendance sheet in Excel, step by step

These steps build an attendance sheet with working hours that can feed payroll. They work equally for an employee attendance timetable or a staff attendance list.

1. Staff sheet. One column for a fixed employee ID, then name, branch or department, scheduled start and scheduled end. The ID is the key, because names get spelled several ways.
2. Daily log sheet. One row per employee per working day, with the columns in the table below. This beats a names-by-days grid because it stores times and can be filtered by month or branch.
3. Drop-down lists. Use data validation for the branch and day-code columns, so nobody types a value their own way.
4. Formula columns. Late minutes and hours worked, as in the next section.
5. Protection. Lock the formula columns and header row with a password from the Review tab.
6. Monthly summary. A third sheet with one row per employee and columns for days present, days absent, leave, total late minutes and total hours, all using COUNTIFS and SUMIFS on the daily log.

Column	What goes in it	Why it matters
Date	A real Excel date, not text	Lets you filter by month and count working days
Employee ID	A stable code	Links the log to the staff list and payroll
Branch / department	One value from a drop-down	Totals by location without separate files
Scheduled start	E.g. 08:30	Late minutes are measured against it
Clock-in	Actual arrival time	The raw fact; never overwrite it
Clock-out	Actual departure time	Needed for hours worked
Day code	P (present), A (absent), L (approved leave), H (public holiday)	Separates unpaid absence from paid leave
Late minutes	Formula	Feeds the lateness deduction
Hours worked	Formula	Feeds overtime and part-time pay
Notes / approved by	Free text plus a name	The audit trail for any dispute

For split shifts (morning and evening), do not put both shifts on one row. Give each shift its own row with its own start time, so lateness stays correct. And if you need an English and an Arabic version, only the headers change; the formulas stay the same.

Formulas for lateness and hours in Excel

Excel stores a time as a fraction of a day, so 12:00 is 0.5. Microsoft's guide to calculating the difference between two times shows the core move: subtract the start from the end, then multiply by 1440 for minutes or 24 for hours. Almost every error comes from the edge cases.

1. Negative lateness. =(E2-D2)*1440 goes negative for early arrivals, which then cancels another day's lateness in the total. Use =MAX(0,ROUND((E2-D2)*1440,0)).
2. Missing clock-out. A blank counts as zero, so hours go negative. Use =IF(OR(E2="",F2=""),"CHECK",(F2-E2)*24) and filter for "CHECK" every week.
3. Shifts across midnight. A guard on 20:00 to 08:00 shows minus 12 hours. Use =MOD(F2-E2,1)*24.
4. Totals above 24 hours. A cell formatted h:mm resets every 24 hours. Use [h]:mm, or keep decimal hours.
5. Wrong weekend. Plain NETWORKDAYS assumes Saturday and Sunday. In Egypt and the Gulf use NETWORKDAYS.INTL: code 7 is Friday and Saturday, code 16 is Friday only.
6. Absence and leave in one bucket. Count each day code separately with COUNTIFS and deduct only the A rows.
7. One file per branch. Use one shared file with a branch column instead of pasting five files together at month-end.
8. Unpaid breaks. Daily hours are clock-out minus clock-in minus the break. With clock-in in E, clock-out in F, break start in G and break end in H, use =(MOD(F2-E2,1)-MOD(H2-G2,1))*24. For a fixed 30-minute break, use =(MOD(F2-E2,1)-TIME(0,30,0))*24. Wrap either in ROUND(…,2) before you total the month.

Once each day's hours are right, the next questions are how many of them are overtime and what they are worth in pounds. Our guide to calculating working hours, overtime and pay walks through both with a full numeric example.

An attendance and payroll sheet that calculates salary automatically

You can make the sheet calculate pay by adding a payroll tab that reads the monthly summary: daily wage = monthly wage ÷ 30, hourly wage = daily wage ÷ hours per day, then absence and lateness deductions per your policy. Egypt's Labour Law No. 14 of 2025 treats a month as thirty days unless agreed otherwise. We walk through the formulas with a full example in our guide to payroll software for Egyptian SMBs, so we will not repeat them here. Before you automate any deduction, have the rules reviewed by a legal adviser.

Word, PDF or Google Sheets?

• A Word or PDF attendance sheet is only useful for printing: a sign-in sheet for a training course, or a site list for one day's workers. It calculates nothing, so do not run payroll from it.
• Google Sheets fixes the "one file per branch" problem, because every branch manager writes in the same file from a browser, and the formulas mostly work as they are. It does not fix the fact that someone types the time after it happened.
• An English version for teams with foreign staff only needs translated headers.

Signs your attendance spreadsheet has broken

There is no magic headcount at which Excel fails. Two or more of these signals usually mean the sheet costs more time than it saves:

• The data is typed, not captured. Someone copies times from a paper register, WhatsApp messages or a fingerprint export, and every copy is a chance for a typo.
• You have more than one location. Two branches mean two people entering data in two styles. Our guide to attendance across retail branches explains why errors multiply here.
• Staff work away from a desk. Reps, technicians and guards start the day at a client site, so attendance becomes a phone call. GPS attendance for field teams is the usual fix.
• People sign in for each other. On paper or a shared sheet, anyone can type in a friend's arrival time.
• Payroll turns into a negotiation. Our guide to turning attendance into correct payroll shows how a fixed cut-off and a named approver settle those cases.
• Month-end takes a day or more. That time is a cost, even if it never appears on an invoice.

Excel vs register or fingerprint device vs GPS app

	Excel sheet	Paper register or fingerprint device	Mobile GPS attendance app
Upfront cost	None if you own Office	Device and installation, or just a notebook	Subscription; some apps have a free tier
Who records the time	A person types it	The employee signs or scans in	The employee checks in from their phone
Proof of location	None	Only that they reached the device	Phone location checked against an office radius
Field staff	Poor	One device per site	Works wherever there is coverage
Lateness and absence	Your formulas	Device export, then your formulas	Calculated automatically
Month-end effort	Manual merge and checks	Export, clean, merge	Review and approve
Best fit	Small team, one location	One fixed site with one entrance	Several branches and field teams

A card or fingerprint attendance machine solves identity at one door, but still leaves you exporting a file into Excel every month. We cover device types and costs in our guide to fingerprint attendance machines, and every method side by side in our guide to choosing employee attendance software in Egypt.

A 5-day migration from spreadsheet to app

Start in the first week after payroll closes, and pick one person to own the change.

1. Day 1: Staff list. Export the staff sheet, remove leavers, merge duplicate spellings and enter each employee once in the app. Do not import old history; archive the file.
2. Day 2: Locations and hours. Set the work location and the accepted radius, then the start time. Test a check-in from inside the office and from the pavement outside.
3. Day 3: Roll out to staff. Send the download link, walk each team through their first check-in, and explain plainly what the location check does and does not record.
4. Day 4: Run in parallel. Keep the sheet for the day and compare it with the app, fixing every mismatch before the day ends.
5. Day 5: Reconcile and retire. Match the app's weekly report against the sheet for lateness and absence. When they agree, or every difference is explained, make the sheet read-only.

If the numbers do not agree, extend the parallel run by a week rather than switching on faith.

How Yawmy handles this

Yawmy is an attendance and employee-management app for small and mid-sized teams in Egypt and the Gulf. Based on what is published on the Yawmy website:

• Employees check in from the iOS or Android app, or through a Telegram bot.
• Attendance is GPS-verified against an office radius you set.
• Lateness and absence deductions are calculated automatically into an end-of-month payroll sheet.
• A live dashboard shows hours, lateness and commitment, with staff grouped by branch or department.

Yawmy is free for two employees. The Pro plan is 500 EGP per month or 4,500 EGP per year for unlimited employees. Yawmy does not offer an Excel template download; if the sheet you built still serves a small team well, keep it until the signs above start to show.

FAQ

How do I calculate daily hours from clock-in and clock-out minus breaks?

Subtract clock-in from clock-out, subtract the break, then multiply by 24 to get decimal hours: =(MOD(F2-E2,1)-MOD(H2-G2,1))*24 when breaks are logged, or =(MOD(F2-E2,1)-TIME(0,30,0))*24 for a fixed 30-minute break. MOD keeps overnight shifts from turning negative.

Is there a ready-made Excel attendance template I can download?

Plenty circulate online, but most are a names-by-days grid that does not record times to the minute, and all need adjusting to your hours and holidays. You are better off building the sheet with the six steps above so you understand every formula. Yawmy does not offer a template download.

How do I make a monthly attendance sheet for employees?

Log one row per employee per working day in a daily log, then create a monthly summary sheet with one row per employee. Use COUNTIFS to count present, absent and leave days by code, and SUMIFS to total late minutes and hours for the month.

What is the opening record in an attendance register?

It is a note on the first page that usually gives the business name, opening date, number of numbered pages and the responsible person's name and signature, matched by a closing note at the end. It is an administrative practice that protects against removed pages; if your organisation uses a specific form, follow it.

How do I calculate hours worked and lateness in Excel?

Subtract the scheduled start from the clock-in and multiply by 1440 for late minutes, wrapped in MAX(0, …) to avoid negatives. For hours, subtract clock-in from clock-out and multiply by 24, using MOD for shifts that cross midnight.

How do I set up an attendance sheet for split shifts?

Give each shift its own row in the daily log, with its own start time, instead of putting both shifts on one row. Lateness is then measured against each shift's start, and the monthly summary adds both shifts together per employee.

When should I move from a spreadsheet to attendance software?

When two or more signs appear: data copied from a register or device, more than one branch, field staff, people signing in for each other, recurring deduction disputes, or a month-end close that takes a day. The move takes about five working days.

Written by Nada Hassan, HR-Tech Strategist, Yawmy.

Ready to retire the spreadsheet? Download Yawmy and start free with your first two employees.