Excel Attendance Sheet With Overtime: Setup and Formulas

Formulas & functions · Updated November 18, 2026

Yes. An Excel attendance sheet can calculate shift hours and overtime from employees’ start times, end times, and unpaid breaks. With start time in C2, end time in D2, and break duration in E2, enter this formula in F2 to calculate worked hours as a decimal, including a shift that crosses midnight: =MAX(0,(MOD(D2-C2,1)-E2)*24). The overtime threshold is a separate setting: it depends on the rules and work arrangement that apply, so the workbook should make that threshold easy to review rather than assume one universal rule.

How to structure an attendance sheet

Start with one row for each employee’s shift on a particular date. A practical layout is A for employee name or ID, B for shift date, C for start time, D for end time, E for unpaid break duration, F for hours worked, G for regular hours, and H for overtime hours. Use a separate summary area for weekly totals or approval details. Keeping the original time entries alongside calculated hours makes it easier to check a result than recording only a final number.

Use actual Excel dates and times, not text that merely looks like a time. Enter a date in B and a time such as 8:30 AM in C; enter a break of 30 minutes in E as a time duration such as 0:30. Format date cells as dates, time-entry cells as times, and the calculated hour cells as numbers with two decimal places. In Microsoft 365 for Windows, select the relevant cells and use Home > Number Format to choose an appropriate format. Excel for Mac has similar number-format controls on the Home tab.

For repeated records, make the range an Excel table using Insert > Table and confirm that the table has headers. Tables can expand as you add shifts and make it easier to filter by employee or date. Keep column headings specific—for example, “Unpaid break” rather than simply “Break”—so staff know what to enter.

How the hours formula handles breaks and overnight shifts

Excel stores times as fractions of a day. The MOD function in the example formula calculates the elapsed time even when an end time is earlier on the clock than a start time. For a shift starting at 9:00 PM and ending at 6:00 AM, with a 30-minute unpaid break entered as 0:30, the formula returns 8.5 hours: nine elapsed hours minus half an hour. Multiplying by 24 converts Excel’s fraction-of-a-day result into decimal hours.

This formula assumes the shift is shorter than 24 hours and that the start and end entries represent one continuous shift. For a shift that might last 24 hours or longer, use full date-and-time entries and calculate the difference between those date-time values instead; MOD would wrap the elapsed time back within a single day. The formula also uses MAX to prevent a negative result. That can conceal an incorrect break entry, so check that break duration is not greater than the shift length.

Decimal hours are useful for totals: 8.5 means eight hours and 30 minutes. They are not the same display as hours and minutes. If you instead want a duration display, calculate elapsed time as an Excel time value and format the result as a duration such as [h]:mm; the square-bracketed hours let a total display above 24 hours. Avoid adding decimal-hour values and then formatting them as clock times, because Excel interprets the underlying numbers differently.

Separate regular hours from overtime

Do not build a fixed overtime rule into the hours-worked formula. Put the applicable daily threshold in a clearly labeled cell—for example, J1—and use separate columns for regular and overtime hours. With F2 holding hours worked and J1 holding a threshold expressed in hours, enter this formula in G2 for regular hours: =MIN(F2,$J$1). Enter this formula in H2 for overtime hours: =MAX(0,F2-$J$1). Copy the formulas down the rows or, in an Excel table, use calculated columns so new rows can receive the same calculations.

For illustration only, if J1 is 8 and an employee works 8.5 hours in a shift, the formulas return 8 regular hours and 0.5 overtime hours. If the employee works 7.5 hours, they return 7.5 regular and 0 overtime. The example demonstrates the spreadsheet arithmetic; it does not establish which hours qualify as overtime for a real workplace. Applicable law, contracts, workplace policy, and the way a workweek is defined can affect the correct calculation. Confirm the actual rules with an appropriate payroll or HR professional.

Some arrangements require reviewing a workweek total, a daily total, or other conditions. A daily threshold alone cannot represent every rule, and a weekly threshold alone may also miss daily overtime. If more than one threshold applies, calculate and review each relevant category, taking care not to count the same hours twice. Keep the underlying shift hours intact even if a payroll system ultimately performs the official overtime calculation.

Summarize hours by employee and workweek

A weekly summary helps reveal missed shifts and makes totals easier to compare with payroll records. The following example assumes employee names or IDs in A2:A200, shift dates in B2:B200, and worked hours in F2:F200. Put the employee to summarize in K2 and the first date of the workweek in L2; then enter this formula in M2 to total that employee’s hours from that date up to, but not including, seven days later: =SUMIFS($F$2:$F$200,$A$2:$A$200,K2,$B$2:$B$200,">="&L2,$B$2:$B$200,"<"&L2+7).

For example, if the matching shifts total 45 hours, M2 returns 45. The calculation is simply the sum of the hours in the qualifying rows; it does not decide how many of those hours legally count as overtime. Use a real Excel date in L2 and make sure the chosen start date matches the workplace’s defined workweek. A week that begins on Sunday, Monday, or another day must be summarized consistently. If the organization’s workweek does not match the seven-day window shown, adapt the summary to its actual period.

Make the summary useful for review by adding columns for regular hours, overtime categories if applicable, and supervisor approval. A total can be mathematically correct while still containing a mistyped time or a missing shift. Comparing the summary with schedules and approved time records is an important human check.

Set up entry controls and review the workbook

Data validation and visual cues can reduce avoidable entry errors. Select the time-entry cells and use Data > Data Validation to restrict entries to an appropriate time range where that option fits your process. Restrict dates in the date column if needed. Validation is a prompt, not proof that an entry is correct: a valid time can still be entered in the wrong employee’s row or on the wrong date.

  • Use consistent time entry, such as 8:00 AM, and state whether breaks are unpaid or paid.
  • Freeze the header row through View > Freeze Panes so headings remain visible while scrolling.
  • Filter the table by employee, date, or approval status to investigate unusual totals.
  • Use Home > Conditional Formatting to highlight missing entries or unusually long shifts, then check the highlighted records rather than assuming they are errors.
  • Keep a notes or exception column for corrected punches, approved adjustments, and the reason for a change.

Protect formula cells from accidental editing while leaving entry cells available to staff. Excel’s Review > Protect Sheet command can limit changes to a sheet, but worksheet protection is not a substitute for secure access controls or a reliable audit trail. Keep an unaltered copy of the original records when corrections are needed, and follow the organization’s retention and privacy requirements.

Common spreadsheet mistakes and how to avoid them

A frequent error is entering a break as the number 30 instead of the duration 0:30. Since the formula subtracts an Excel time value, a plain 30 is interpreted as 30 days, not 30 minutes. Another common problem is storing times as text. If Excel does not recognize an entry as a time, calculations may fail or produce unexpected results; re-enter it using a consistent time format and verify the cell’s value.

Overnight shifts are another source of confusion. A basic subtraction such as D2-C2 can be negative when a shift ends after midnight. MOD addresses that case for shifts shorter than 24 hours, but a date-time approach is preferable when the shift’s dates matter or a shift could span more than one day. Also decide how to record a shift split across midnight: one row per shift is often easiest to audit, while separate rows by calendar date may be necessary for some reporting rules.

Do not round every shift to whole hours simply to make the sheet look tidy. Rounding can change a total, especially over many shifts. Keep the underlying calculations at full precision and apply number formatting for display. If a workplace has a specific rounding policy, configure and review it deliberately rather than applying an arbitrary rounding formula. Finally, test the workbook with ordinary, overnight, short, and break-containing shifts before relying on it for a pay period.

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

An Excel sheet can suit a small team with a consistent schedule, a clear approval process, and someone responsible for checking entries and formulas. It is flexible: thresholds, columns, and summaries can be adapted, and the workbook can be filtered or printed. The tradeoff is that people can overwrite formulas, copy the wrong row, or edit a record without a dependable history unless the file is managed carefully.

For multiple locations, rotating schedules, frequent punch corrections, or formal approval and audit requirements, dedicated time-and-attendance or payroll software may be more appropriate. Such systems may provide role-based access, change logs, scheduling, and payroll integrations, but features and prices vary by provider. Compare the actual capabilities, export formats, access controls, and cost with the organization’s needs. Whichever tool is used, preserve the original time records and verify that its overtime settings reflect the rules that apply.

Frequently Asked Questions

Can Excel calculate overtime automatically?
Yes. Formulas can separate hours above a threshold into an overtime column. The sheet only applies the rule it has been configured to use, so verify the applicable overtime conditions before relying on its result.

How do I calculate a shift that ends after midnight?
For a shift shorter than 24 hours, use =MAX(0,(MOD(D2-C2,1)-E2)*24), assuming the start, end, and break are in C2, D2, and E2. MOD handles the midnight crossing; use date-and-time entries for longer or more complex shifts.

Should overtime be calculated daily or weekly?
That depends on the rules and work arrangement that apply. Some calculations may consider more than one period or condition. Confirm the required method, then configure the workbook to match it without counting the same hours twice.

How should I enter a 30-minute break?
Enter it as an Excel duration such as 0:30 in the break column, not as the number 30. Label the column to make clear whether the break is unpaid and therefore subtracted from worked hours.

Can I use the sheet as an official payroll record?
A workbook can store and summarize time entries, but whether it meets workplace, payroll, or record-retention requirements depends on the organization and applicable rules. Keep records secure, preserve corrections, and confirm requirements with a qualified professional.

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.