Attendance Log Excel Template: Build One That Works

How-to basics · Updated November 21, 2026

An attendance log Excel template is a worksheet for recording who attended, when they arrived or left, and any relevant notes. A practical starting layout is Date, Name or ID, Status, Time In, Time Out, Break Minutes, Hours, and Notes; you can copy these headings into row 1 and add one row for each person on each date.

What an attendance log records

An attendance log is a structured record of attendance, not just a list of names. The useful details depend on the setting. A classroom may need a class or session and a status such as present, absent, or excused. A workplace may need start and end times, break information, and a way to distinguish late arrivals from absences. A training session might record the event name and whether a participant attended in person or remotely.

Decide what one row represents before you build the sheet. For most logs, one row per person per session or workday is easy to sort, filter, and summarize. If you instead make one column for every date and one row for every person, the layout can be convenient for marking a month at a glance, but it is less flexible for filtering individual dates or calculating totals. A row-based log is usually a better base for ongoing records and reports.

Keep the status choices consistent. For example, “Absent,” “Excused,” and “Late” should each have one spelling throughout the workbook. If one person enters “Tardy” while another enters “Late,” a simple count may treat them as separate categories. The meanings of statuses should follow the organization’s own attendance policy; the spreadsheet should record that policy rather than invent one.

A ready-to-copy Excel layout

Enter these headings across row 1, in this order:

  • Date
  • Name or ID
  • Status
  • Time In
  • Time Out
  • Break Minutes
  • Hours
  • Notes

Use one row for each person’s attendance on a particular date. A sample row might read: 11/03/2026, Jordan Lee, Present, 9:00 AM, 5:30 PM, 30, 8, and “Left at normal time.” This example assumes a half-hour break is deducted from the time between arrival and departure. In a school or event log, the time and hours columns may be unnecessary; remove fields that are not needed instead of collecting extra information by default.

If the log covers several classes, locations, or departments, add a column such as Session or Team. A unique ID can help distinguish people with the same name. Avoid combining multiple people or dates in one cell: separate records make filtering and summaries more dependable. Keep notes brief and factual, and do not use the notes field as a place for unrelated personal details.

Set up the worksheet in Microsoft Excel

  1. Open a blank workbook in Excel for Microsoft 365 on Windows and type the headings into row 1. Keep each field in its own column.
  2. Select the header row and a few rows below it, then choose Insert > Table. Confirm that “My table has headers” is selected. An Excel table expands as you add records and makes it easier to sort and filter the log.
  3. Format the Date column as a date and Time In and Time Out as times. Select the relevant cells, then use Home > Number to choose an appropriate number format. Format Hours as a number, such as two decimal places, if you plan to calculate totals.
  4. Use Data > Data Validation to create a dropdown for Status. Under Allow, select List, then enter the approved choices in Source, separated by commas—for example, Present, Late, Absent, Excused. If those choices may change, store them in a separate list and use that range as the source.
  5. Freeze the headings with View > Freeze Panes > Freeze Top Row if the log will be long. You can filter the table by date, person, or status using the arrows in the header row.

The ribbon names above describe Excel for Microsoft 365 on Windows. Excel for the web has similar table, validation, and formatting tools, though some controls may appear in different places or have fewer options. Excel for Mac also supports tables and data validation; ribbon labels and dialog layouts can differ by release. If you share the workbook, recipients may use a different Excel version, so avoid relying on a feature they cannot access.

Calculate hours and attendance totals

A formula can calculate worked or attended hours from the recorded times. With Time In in D2, Time Out in E2, and Break Minutes in F2, enter this formula in G2, then fill it down:

=IF(OR(D2="",E2=""),"",MAX(0,MOD(E2-D2,1)*24-F2/60))

This returns a blank when either time is missing. For a 9:00 AM start, 5:30 PM finish, and a 30-minute break, it returns 8. The MOD calculation also handles a shift that passes midnight; for instance, 10:00 PM to 6:00 AM is treated as eight elapsed hours before any break deduction. Check that time entries are stored as Excel times, not text, and that the break value is entered in minutes. This calculation does not account for rounding rules, paid breaks, overtime, or workplace policy.

To count status entries, with statuses in C2:C101, use the following formula to count records marked Present:

=COUNTIF(C2:C101,"Present")

If that range contains 18 Present records, the result is 18. To count Present and Late together, still using C2:C101 as the status range, use:

=COUNTIF(C2:C101,"Present")+COUNTIF(C2:C101,"Late")

If there are 18 Present and 2 Late records, the result is 20. These are record counts, not necessarily unique people; a person attending ten sessions will appear ten times.

You can calculate a percentage, but define the denominator first. With statuses in C2:C101, the formula below counts Present and Late as attended and excludes Excused and blank cells from the denominator:

=IFERROR((COUNTIF(C2:C101,"Present")+COUNTIF(C2:C101,"Late"))/(COUNTIF(C2:C101,"Present")+COUNTIF(C2:C101,"Late")+COUNTIF(C2:C101,"Absent")),0)

With 18 Present, 2 Late, and 3 Absent records, it returns 20 divided by 23, or about 86.96%. Format the result as a percentage. This is only one possible definition of an attendance rate; decide how excused absences, partial attendance, and other statuses should be treated before using a percentage for reporting.

Make entries consistent and easier to review

Dropdowns reduce spelling variations, while basic visual cues can help a reviewer spot records that need attention. Select the Status cells and choose Home > Conditional Formatting > Highlight Cells Rules to apply a highlight to a particular value, such as Absent. Use clear, restrained formatting so color supports the text rather than replacing it; status words should remain readable if the workbook is printed in grayscale.

For a reliable routine, consider these checks when entering or reviewing records:

  • Confirm the date and person before entering a status, especially when several sessions occur on the same day.
  • Use the approved status dropdown rather than typing a new variation.
  • Check that Time Out is later than Time In unless the shift intentionally crosses midnight.
  • Leave time fields blank when they are not collected, rather than entering a made-up time.
  • Filter for blank statuses or missing times before preparing a report.
  • Keep an unchanged copy or use your organization’s normal backup process before making large edits.

Table filters are useful for reviewing a single person, date range, or status. For a summary by month, you can filter the Date column or create a PivotTable from the table using Insert > PivotTable. A PivotTable can group and summarize records, but its result depends on clean source data and appropriate fields. Check that it includes the intended date range and status categories before relying on a report.

Choose the right template for the job

A simple register with a date, name, and status is often enough for a small class, club, or meeting. Add clock-in and clock-out fields only when elapsed time matters. For shift work, include break minutes and consider whether overnight shifts need to be handled. For recurring sessions, a Session column can make it possible to distinguish attendance at different events on the same date.

Before adopting a template, look for a clear heading row, one record per row, consistent date and time formats, a defined set of statuses, and no unexplained formulas or hidden sheets. A prebuilt workbook may save setup time, but inspect formulas and validation lists before entering real records. The right amount of detail is the minimum needed to answer the questions the log is intended to answer.

Excel is a practical choice when a small group needs a familiar, editable file and does not need simultaneous check-in workflows or automated reminders. A paper register may be simpler where devices are unavailable. A dedicated attendance system may suit larger groups that need role-based access, live check-in, or integration with other systems. Each alternative has its own costs and privacy considerations; compare actual features and terms rather than assuming a spreadsheet or app is automatically more secure.

Common problems and privacy considerations

A frequent source of errors is treating numbers that look like dates or times as plain text. Text values may not sort or calculate as expected. Apply date and time formatting, enter values consistently, and test formulas on a few sample rows before filling the sheet. Another common issue is counting records as if they were people: a COUNTIF total counts matching cells, so repeated attendance by the same person increases the total.

Duplicate rows can also distort totals. A person may legitimately have multiple sessions on one date, so do not delete apparent duplicates until you check what each row represents. If the log should allow only one record per person per session, agree on how to identify that combination and review it consistently. Avoid merging cells in the data area, because merged layouts interfere with sorting and filtering.

Attendance records can contain personal information. Limit access to people who need it, use an approved storage location, and follow the organization’s retention and handling rules. A password on a worksheet can prevent accidental changes, but it should not be treated as strong protection for sensitive data. Do not email or share a log more broadly than necessary, and avoid including sensitive explanations in Notes unless there is a clear, authorized need.

Frequently Asked Questions

How do I make an attendance sheet in Excel?
Enter one field per column, such as Date, Name, Status, and optional Time In and Time Out. Select the data and choose Insert > Table, then add a status dropdown with Data > Data Validation if consistent choices are useful.

Can Excel calculate attendance hours automatically?
Yes. If the start and end times are entered as Excel times, a formula can calculate elapsed hours and subtract a break. Check the result against the organization’s rules for breaks, rounding, and overnight shifts before using it for official records.

How do I count absences in an attendance log?
Use COUNTIF on the Status column with the criterion Absent. For example, if statuses are in C2:C101, =COUNTIF(C2:C101,"Absent") counts cells marked Absent. The result counts records, not distinct people.

Should attendance be recorded by month or in one continuous log?
Either can work. A continuous table is easier to filter and summarize across dates, while separate monthly sheets may be easier for some people to navigate. If you use separate sheets, keep the same column names and status choices on each one.

Is an Excel attendance log suitable for official payroll or compliance records?
It depends on the organization’s requirements, applicable rules, and how the workbook is controlled. A basic template can help organize entries, but it does not itself verify records or ensure compliance. Confirm required procedures with the responsible administrator or a qualified professional.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.