Excel Time Clock: Track Shifts, Breaks, and Overtime

How-to basics · Updated November 18, 2026

An Excel time clock is a workbook for recording employees’ clock-in and clock-out times, calculating shift hours, and summarizing totals. To make a basic one, create columns for employee, date, start time, unpaid break, end time, and hours; enter each punch and use a formula to calculate the shift. Excel can organize and calculate time, but it does not provide the identity checks or tamper-resistant punch records of dedicated time-clock software.

What an Excel time clock can do

A spreadsheet time clock can be as simple as a personal shift log or as involved as a shared timesheet for a small team. At its simplest, each row represents one employee’s shift. The workbook stores the date and time entries, calculates elapsed hours, and can total hours by person or week. You can also use it to flag missing punches, sort records, and prepare figures for review.

It is useful to distinguish a time clock from a schedule. A schedule says when someone is expected to work; a time clock records when they actually worked. A spreadsheet can contain both, but planned shift times should be clearly separated from actual punches so that a scheduled start is not mistaken for a recorded start.

Excel does not automatically know who is entering a time, whether the entry was made at the moment of the punch, or whether a saved value was changed later. It can help calculate time from entries, but the reliability of the result depends on how the workbook is designed, shared, checked, and protected.

Set up a simple timesheet

For a basic workbook in Excel for Microsoft 365 on Windows, put descriptive headers in row 1 and start entering shifts in row 2. One practical layout is:

  • A: Employee name or ID
  • B: Shift date
  • C: Clock-in time
  • D: Unpaid break start
  • E: Unpaid break end
  • F: Clock-out time
  • G: Hours worked

Keep one shift per row, including for employees who work more than one shift in a day. Use an actual date in column B and actual Excel time values in the punch columns, rather than typing a sentence such as “Monday morning.” Select the data and choose Insert > Table to make a table. A table can expand as rows are added, and its filters make it easier to view one employee or date range.

For a shared workbook, decide who may enter or correct punches and how a correction should be documented. A separate notes or correction column can record why a value changed and who approved it. Avoid putting multiple employees’ shifts into a single cell: separate rows are much easier to sort, total, and review.

Enter and format punch times

Enter times consistently, for example 8:00 AM or 4:30 PM, and use one date convention throughout the workbook. Excel stores dates and times as numeric values, even though it displays them in a readable format. If a time is left-aligned or treated as text, formulas may not calculate it as intended. Select a cell and check the formula bar, or test the entry in a calculation before relying on a full timesheet.

To format time cells, select columns C:F, press Ctrl+1 on Windows, choose the Number tab, select Time, and choose a display format. Formatting changes how a value appears, not the underlying value. For dates, use a consistent date format so entries such as 03/04 are not ambiguous to people who use different regional conventions.

On Windows, Ctrl+; inserts the current date into the selected cell, and Ctrl+Shift+; inserts the current time as a value. These shortcuts can help with manual entry, but they do not create an automated punch system. Excel for Mac has different shortcut behavior depending on the keyboard and settings; use Excel Help’s instructions for inserting the current date or time if the Windows shortcuts do not apply.

Calculate hours, including overnight shifts

With employee in A, date in B, start in C, break start in D, break end in E, and finish in F, put this formula in G2 and fill it down. It returns decimal hours and allows the break cells to remain blank when there was no unpaid break:

=IF(OR(C2="",F2=""),"",24*(MOD(F2-C2,1)-IF(AND(D2<>"",E2<>""),MOD(E2-D2,1),0)))

For a shift from 8:00 AM to 4:30 PM with an unpaid break from 12:00 PM to 12:30 PM, the result is 8: the elapsed shift is 8.5 hours and the break is 0.5 hours. The formula uses MOD to handle a shift that crosses midnight. For example, a 10:00 PM start and 6:00 AM finish gives eight elapsed hours before subtracting any recorded unpaid break.

The formula assumes each row is one shift and that break start and end are both entered if an unpaid break is to be deducted. If only one break time is entered, it does not subtract a partial break. Confirm that the displayed decimal result makes sense for sample shifts before copying the formula throughout a live workbook. Decimal hours are convenient for totals: 7.5 means seven and a half hours, not 7 hours and 50 minutes.

Summarize weekly hours and overtime carefully

To total hours for an employee, use a weekly summary rather than adding up visible rows by eye. For example, assume employee names are in A2:A100, shift dates in B2:B100, and calculated decimal hours in G2:G100. Put the employee to summarize in J2 and the first day of the chosen seven-day period in K2. This formula totals shifts whose recorded dates fall in that period:

=SUMIFS($G$2:$G$100,$A$2:$A$100,J2,$B$2:$B$100,">="&K2,$B$2:$B$100,"<"&K2+7)

If the matching rows contain 8, 8.5, 9, 8, and 9 hours, the result is 42.5 hours. The seven-day period must match the workweek you intend to summarize. This example assigns the whole shift to the date in column B, so a shift that crosses the boundary between workweeks is not split between them.

Overtime calculations depend on the applicable rules and the employer’s defined workweek; a spreadsheet formula should not be treated as a determination of what is legally owed. As a purely illustrative calculation, if a policy uses a 40-hour weekly threshold, the layout above can calculate hours above that threshold with this formula in L2:

=MAX(0,SUMIFS($G$2:$G$100,$A$2:$A$100,J2,$B$2:$B$100,">="&K2,$B$2:$B$100,"<"&K2+7)-40)

For the 42.5-hour example, it returns 2.5. This is only arithmetic based on the stated threshold and date grouping; it does not account for every possible rule, exception, or pay calculation. Check the actual workweek definition and applicable requirements with an appropriate professional rather than assuming a spreadsheet’s default threshold is correct.

Reduce entry mistakes and review the results

Time-entry errors can produce plausible-looking totals, so build review into the process. A useful routine is to compare the timesheet with the source records, review unusual shifts, resolve missing punches, and keep a record of approved corrections. Conditional formatting can make blanks or unusually long shifts easier to notice, but a visual warning is not proof that an entry is wrong.

  • Use consistent employee names or IDs; spelling variations can split one person’s totals across multiple records.
  • Check that every completed shift has a start and finish time, and that any deducted break has both endpoints.
  • Review overnight shifts and shifts that cross the selected weekly boundary.
  • Keep original punch information and correction notes when an entry must be changed.
  • Reconcile weekly totals against the records used by the organization before using them for payroll processing.

To reduce inconsistent names, you can create a staff list and use Data > Data Validation to provide a drop-down list. This can prevent entries such as “Sam Lee” and “Samuel Lee” from being treated as separate employees. It does not verify who made a punch, however, and data validation can be bypassed or changed by someone with access to the workbook.

Sharing, privacy, and reliability limits

A time record contains information about when people work, so limit access to people who need it and follow the organization’s retention and privacy practices. Store the workbook in an approved location, use appropriate account and file permissions, and avoid emailing uncontrolled copies that can become inconsistent. A password or sheet-protection setting can discourage accidental edits, but it should not be mistaken for a secure audit trail or strong access control.

Excel for Microsoft 365 can support co-authoring when a workbook is stored in a supported cloud location and shared appropriately. That can make simultaneous access more practical than passing around separate files, but it does not turn a spreadsheet into a clock terminal. People may still enter a time later, share credentials, or alter an entry if their permissions allow it. Keep a controlled master copy and make backups before substantial changes.

Also consider what happens when a formula is overwritten, a row is deleted, or a workbook is copied without its latest entries. Protecting formula cells and separating input cells from calculated cells can reduce accidental damage, but neither removes the need for review. For important records, a system designed for timekeeping may offer better controls and clearer histories of changes.

Excel timesheet versus dedicated time-clock software

Excel is often a reasonable choice for a very small team, a short-term project, or a personal record when entries are reviewed and the consequences of a mistake are manageable. It is flexible, familiar, and easy to adapt for unusual shift patterns. It can also be useful for analyzing exported records from another system.

Dedicated time-clock software is more suitable when a business needs employees to punch in and out through a defined process, managers to approve changes, automated reminders, detailed edit histories, or integration with scheduling and payroll systems. Features vary by product, so compare how each handles breaks, overnight work, permissions, corrections, exports, and the organization’s specific rules. A spreadsheet may still be useful for reporting, but it should not be presented as equivalent to a system that verifies and records punches.

Frequently Asked Questions

Can Excel automatically clock employees in and out?

Not by itself in the way a dedicated time-clock app or terminal does. Excel can store a manually entered time or use a macro or other automation, but a workbook alone does not confirm a person’s identity or prove that a punch was made at that moment.

How do I calculate hours when a shift ends after midnight?

Use a formula that handles the time difference across midnight, such as the MOD-based formula above. Enter the shift’s date consistently and check how your weekly summary treats a shift that crosses into a different workweek.

Should I enter hours as decimals or hours and minutes?

Either can work, but use one approach consistently. Decimal hours make addition and many payroll exports straightforward: 7.5 is 7 hours 30 minutes. A displayed duration such as 7:30 is easier to read as a clock-style interval, but it is not the same numeric representation as 7.5.

Can I use an Excel time clock for payroll?

You can use a workbook to organize and calculate time records, but check the entries and the applicable pay rules before using totals for payroll. A formula cannot establish whether a punch is accurate or whether every relevant rule has been applied.

Is an Excel time clock free?

There is no separate time-clock charge for making a basic workbook if you already have access to Excel, but the software itself may require a Microsoft 365 license. Setup, reviews, corrections, storage, and recordkeeping also take time, and dedicated services may have subscription costs.

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.