Excel Attendance Sheet With Time: Setup and Formulas

Formulas & functions · Updated November 18, 2026

To make an Excel attendance sheet with time, create columns for the date, employee, scheduled start, clock-in, clock-out, break minutes, and hours worked. With clock-in in D2, clock-out in E2, and break minutes in F2, enter =IF(OR(D2="",E2=""),"",ROUND(MOD(E2-D2,1)*24-F2/60,2)) in G2 to calculate daily hours, including shifts that cross midnight.

What a time-based attendance sheet records

A useful attendance sheet is more than a list of names and dates. It records when each person was expected to start and when they actually started and finished, so the hours calculation can be checked against the original entries. A simple layout works for one employee or many employees, provided each row represents one person’s shift on one date.

For example, use columns for Date, Employee, Scheduled start, Clock-in, Clock-out, Break minutes, Hours worked, and Late minutes. Keep scheduled time separate from actual time: a scheduled start of 8:00 AM and an actual clock-in of 8:07 AM are different facts and may be used for different reports. If employees have multiple shifts in one day, enter each shift on its own row rather than trying to squeeze several time pairs into one record.

This structure is suited to straightforward attendance tracking. It is not automatically a complete payroll or timekeeping system: it does not, by itself, determine overtime rules, paid-break policy, leave eligibility, or which entries have been approved. Those decisions depend on your organization’s policies and applicable requirements.

Set up the columns and turn the range into a table

In a blank worksheet, put the column names in row 1 and begin entering records in row 2. A sample layout is A for Date, B for Employee, C for Scheduled start, D for Clock-in, E for Clock-out, F for Break minutes, G for Hours worked, and H for Late minutes. Use one row per shift and avoid merged cells, blank separator rows, or multiple people’s names in one cell; those choices make sorting, filtering, and summarizing more difficult.

Select the headers and a few rows of data, then choose Insert > Table and confirm that My table has headers is selected. When you add a formula to a table column, Excel generally fills that formula down the column and continues it for new rows. Tables also provide filter arrows, which help locate an employee or date without changing the underlying records.

Use consistent entries in the Employee column. For a small sheet, typing the same name each time may be enough; for a larger one, a controlled list can reduce spelling variations that split one person into separate results. Keep the original clock times in their own columns. Avoid replacing them with rounded or edited values just to make a weekly total look neat.

Enter and format dates and times

Excel stores dates and times as numeric values, even though it displays them in familiar formats. A time is a fraction of a day, so 12:00 PM is halfway through a day and 6:00 AM is one quarter of a day. This is why subtracting two valid time values can produce an elapsed duration. If a cell contains text that looks like a time but is not recognized as a time value, arithmetic may fail or give an unexpected result.

Enter a time such as 8:05 AM or 4:35 PM, using the same convention throughout the sheet. Select the time columns and use Home > Number Format to choose a time display, such as 1:30 PM. Format the Date column as a date, and the Break minutes column as a number with zero decimal places. On Windows, Ctrl+1 opens Format Cells; on Mac, Command+1 opens the equivalent dialog. The ribbon paths are broadly similar in Excel for Microsoft 365 on Mac, though the layout can vary slightly.

Do not type a break of 30 minutes as 0:30 if your formula expects a number of minutes. In this layout, enter 30 in F2. If you prefer to enter breaks as elapsed times, use a different formula and keep that input format consistent. A clear header such as Break minutes helps prevent this common mix-up.

Calculate daily hours worked

With clock-in times in D2, clock-out times in E2, and break durations in F2 expressed as minutes, put this formula in G2 and fill it down: =IF(OR(D2="",E2=""),"",ROUND(MOD(E2-D2,1)*24-F2/60,2)). The blank check leaves the result empty until both clock times are present. MOD handles a shift that passes midnight, while multiplying by 24 converts Excel’s fraction-of-a-day result into decimal hours. Dividing the break minutes by 60 converts the break to hours before subtracting it.

For example, if D2 is 8:05 AM, E2 is 4:35 PM, and F2 is 30, the elapsed time is 8.5 hours. Subtracting a 30-minute break gives 8.0 hours, which is the displayed result. The formula rounds the result to two decimal places. Decimal hours are useful for adding and comparing totals, but 8.5 means eight and a half hours, not 8 hours and 50 minutes.

If you would rather display an elapsed duration as hours and minutes, use a duration formula instead. With the same layout and break in F2 as minutes, enter =IF(OR(D2="",E2=""),"",MOD(E2-D2,1)-F2/1440) in G2, then format G2 or the G column with the custom number format [h]:mm. The example above displays as 8:00. Square brackets matter: ordinary h:mm can roll over after 24 hours when showing a total across multiple shifts, while [h]:mm displays accumulated hours.

Handle overnight shifts and breaks carefully

The MOD calculation treats a clock-out earlier than a clock-in as a shift that continued into the next day. For instance, a 10:00 PM clock-in and a 6:00 AM clock-out produce eight elapsed hours before breaks. With a 30-minute break entered as 30, the daily formula returns 7.5. This is convenient for a single overnight shift when only the times of day are recorded.

That convenience has a limitation: the formula cannot infer the date of every event. If a shift can last longer than 24 hours, if an employee clocks out on a different date, or if records need precise timestamps, use separate date-and-time values for the start and end rather than only time-of-day values. Keep the dates visible so a reviewer can understand which work period a row represents.

Break treatment should also be explicit. The formula subtracts whatever number is entered in Break minutes; it does not decide whether a break is paid, required, or valid. If a person takes two breaks, enter their combined duration in minutes, or add separate break columns if you need to retain each event individually. Never assume a blank break cell means a specific policy unless the sheet’s instructions say so.

Track lateness and weekly totals

If C2 contains a scheduled start and D2 contains clock-in, you can calculate late minutes for a same-day shift. With those columns holding valid Excel time values, enter =IF(OR(C2="",D2=""),"",MAX(0,ROUND((D2-C2)*1440,0))) in H2. Multiplying the time difference by 1,440 converts days to minutes; MAX prevents an early arrival from becoming a negative lateness value. A scheduled start of 8:00 AM and clock-in of 8:07 AM returns 7.

This simple lateness formula assumes the scheduled start and clock-in are on the same date and that early arrivals count as zero late minutes. It does not apply a grace period, handle overnight schedule comparisons, or decide how lateness should be treated. If a policy allows five minutes before counting lateness, for example, that rule must be deliberately incorporated and documented rather than assumed by Excel.

For a weekly total, place one employee’s five daily decimal-hour results in G2:G6 and enter =SUM(G2:G6) in G7. If each of the five shifts is 8.0 hours, the result is 40.0. Do not put a weekly total inside the same table as daily shift records unless you intend it to be another data row; a separate summary area or PivotTable is clearer and less likely to be included accidentally in later totals.

Make the sheet easier to check and maintain

Attendance records are more dependable when the sheet makes missing or unusual entries visible. Use clear headers, a consistent date range, and a separate column for each kind of value. You can apply conditional formatting to highlight blank clock-out values or unusually long shifts, but a highlight is a review prompt, not proof that a record is wrong. Check the underlying entry before changing it.

Useful practices include:

  • Keep an unchanged copy of the original entries before making corrections, and note who made a correction and when if your process requires a record of changes.
  • Use filters to review one employee or date range at a time, and confirm that every shift has both a clock-in and clock-out.
  • Check that break minutes are numeric and nonnegative, and investigate a negative hours result rather than hiding it with formatting.
  • Reconcile a sample of calculated shifts against the original time records before relying on a new workbook.
  • Limit workbook access to people who need it. A spreadsheet is editable, and a password or protected sheet should not be treated as a tamper-proof audit trail.

For validation, select the Break minutes cells and choose Data > Data Validation to restrict entries to whole numbers greater than or equal to zero. The exact dialog options depend on the selected validation type. Validation can catch some accidental inputs, but it cannot confirm that the reported break duration is accurate.

Common problems and how to fix them

A result of 0.33 instead of 8:00 usually means the duration is being shown as a fraction of a day. Use the decimal-hours formula that multiplies by 24, or apply the [h]:mm duration format to the elapsed-time formula. Conversely, a result such as 8:30 may be a time duration, not the decimal value 8.5; these are two display choices for the same length of time.

A formula that returns a strange negative number often points to text-formatted times, an incorrect column reference, or a shift/date situation the formula does not represent. Re-enter the time as a recognized value, check the formula’s cell references, and confirm that the correct overnight approach is being used. If a formula appears as text in the cell, change the cell format from Text to General, then re-enter the formula.

Unexpectedly large totals can come from entering break time in the wrong unit, summing a weekly total row along with daily rows, or including duplicate shifts. A blank result may simply mean one of the required clock times has not yet been entered. Before altering a formula, inspect a single example row and calculate its elapsed time manually; that often reveals whether the problem is the entry, the display format, or the calculation.

When a spreadsheet is enough—and when to use another tool

An Excel sheet can be a practical choice for a small team, a temporary event, or a simple record where an authorized person enters and reviews times. It is flexible, easy to adapt, and can calculate totals without a subscription to a dedicated service. It also requires someone to maintain formulas, check entries, manage versions, and control access.

A dedicated time-clock or workforce system may be more suitable when many people need to submit entries, approvals must be tracked, schedules change frequently, or records need an organized audit history and payroll integration. Compare tools based on the specific workflow, including who can edit records, how corrections are logged, what reports are available, and how data can be exported. Do not assume that an Excel total automatically satisfies payroll or recordkeeping requirements; check the relevant organizational rules and qualified guidance.

Frequently Asked Questions

How do I calculate hours worked in Excel with a lunch break?
Record clock-in and clock-out as Excel times, enter the break duration in minutes, and use the daily-hours formula above. It subtracts the break from elapsed time; confirm that the break unit and treatment match your sheet’s policy.

How do I show total hours over 24 hours?
Use an elapsed-duration result and format the cell with the custom number format [h]:mm. The brackets tell Excel to keep accumulating hours instead of restarting the display at 24.

Can Excel calculate an overnight shift?
Yes. The MOD-based formula handles a single shift that starts and ends on different sides of midnight when only time-of-day values are used. Use full date-and-time values when shifts may exceed 24 hours or exact event dates matter.

Should attendance totals be decimal hours or hours and minutes?
Either can work. Decimal hours are convenient for arithmetic, so 7.5 represents seven and a half hours. The [h]:mm display is easier to read as elapsed time, so the same duration appears as 7:30.

Why is my Excel attendance formula showing a decimal?
Excel stores time as a fraction of one day. Multiply an elapsed time by 24 to return decimal hours, or format the elapsed-time result as [h]:mm to show hours and minutes.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.