Excel Time Card Formula: Track Hours and Overtime

Formulas & functions · Updated November 20, 2026

For a basic Excel time card, enter a start time in B2, an end time in C2, and unpaid break minutes in D2; put this formula in E2 to calculate hours worked: =IF(OR(B2="",C2=""),"",MOD(C2-B2,1)*24-D2/60). For example, 8:00 AM to 4:30 PM with a 30-minute unpaid break returns 8 hours. The formula also handles a shift that crosses midnight.

Set up a simple time card

Create one row for each employee and workday, with clear column headings. A practical layout is:

  • A: Work date
  • B: Clock-in time
  • C: Clock-out time
  • D: Unpaid break, in minutes
  • E: Hours worked, calculated by the formula

Enter real Excel date and time values, not text that merely looks like a time. For example, type 8:00 AM or 8:00, depending on your preferred display, and format the cell as a time. Enter the break as a number of minutes, such as 30 for half an hour. In Microsoft 365 for Windows, select the time cells and use Ctrl+1 to open Format Cells, then choose a Time format. On Mac, Command+1 opens Format Cells.

Put the formula in E2, then copy it down the column for additional shifts. If you convert the range to an Excel table with Insert > Table, formulas entered in a calculated column can fill down automatically. Keep one employee’s shifts together if you plan to total hours by week; a separate employee column is useful when one sheet tracks multiple people.

How the hours formula works

The formula subtracts the start time from the end time. Excel stores times as portions of a 24-hour day, so multiplying the difference by 24 converts it to decimal hours. The MOD function handles a clock-out time earlier than the clock-in time by treating it as the next day. Dividing break minutes by 60 converts them into hours before subtracting them. IF and OR leave the result blank until both clock times are entered.

For the worked example, 4:30 PM minus 8:00 AM is 8.5 hours. A 30-minute break is 30/60, or 0.5 hours. Therefore, the result is 8.5 – 0.5 = 8. The result is a decimal number, which is convenient for adding hours and calculating pay amounts from an hourly rate. Do not enter a break as 0:30 in the minutes column: that cell is expected to contain the number 30.

For an overnight example, a shift from 10:00 PM to 6:00 AM with a 30-minute unpaid break returns 7.5 hours: eight hours elapsed minus half an hour. Because the formula uses MOD, it can handle that one-midnight crossing without an extra date column. It is intended for shifts shorter than 24 hours; time-only entries cannot reliably distinguish a shift longer than a day or multiple clockings on the same date.

Choose decimal hours or elapsed time

Decimal hours are often easiest to total and use in other calculations: 7.5 means seven and a half hours. If you would rather see a duration such as 7:30, use a time-duration formula instead. With clock-in in B2, clock-out in C2, and unpaid break minutes in D2, enter this in E2:

=IF(OR(B2="",C2=""),"",MOD(C2-B2,1)-D2/1440)

For the 8:00 AM to 4:30 PM example with a 30-minute break, this returns 8:00 as an elapsed time. Format the result cells with the custom format [h]:mm, rather than a standard time-of-day format. The square brackets let a total display beyond 24 hours; for example, a weekly total of 40 hours remains 40:00 instead of wrapping around to 16:00. In Windows Excel, select the cells, press Ctrl+1, choose Custom, and enter [h]:mm. On Mac, use Command+1 to open the formatting dialog.

Do not add decimal-hour results and duration results as if they were the same kind of value. Choose one representation for the hours column and use it consistently. You can keep a decimal-hours column for calculations and a separate formatted duration column for a more clock-like display, but that is usually unnecessary for a straightforward time card.

Total daily and weekly hours

If E2:E6 contains five daily decimal-hour results for one employee, put a weekly total in E7 with this formula:

=SUM(E2:E6)

For example, daily results of 8, 7.5, 8, 8, and 6.5 add to 38 hours. If E2:E6 contains elapsed-time values instead, the same SUM formula works, but format the total cell as [h]:mm so totals above 24 hours display correctly. Check that the selected range includes all the intended shifts and does not include a previous total row.

For a larger sheet with multiple workers, avoid summing every row into one company-wide total when you need each person’s hours. One simple approach is to filter the employee column and total only the displayed rows using a suitable subtotal method; another is to create a PivotTable that groups hours by employee and date. Whichever approach you use, confirm the employee and week boundaries before relying on the result.

Calculate overtime carefully

Overtime rules depend on the applicable workplace policy and law, and a flat weekly threshold is not suitable for every situation. For a purely illustrative setup where 40 hours is the threshold, suppose F2 contains one employee’s weekly total in decimal hours. Put regular hours in G2 with:

=MIN(F2,40)

With F2 equal to 43, this returns 40 regular hours. With the same weekly total in F2, put overtime hours in H2 with:

=MAX(0,F2-40)

That returns 3 overtime hours when F2 is 43, and zero when F2 is 38. These formulas only split hours around the example threshold; they do not determine the correct workweek, eligible hours, premium rate, or legal treatment of breaks. Confirm the rules that apply to the job with the appropriate employer or qualified professional before using a spreadsheet for payroll decisions.

Use full dates for longer or more complex shifts

Time-only entries are convenient for ordinary shifts, but using complete date-and-time values is safer when a shift crosses midnight and the actual dates matter. For example, store the full start date and time in A2 and the full end date and time in B2, including the next calendar date for an overnight shift; enter unpaid break minutes in C2. Put this formula in D2:

=IF(OR(A2="",B2=""),"",(B2-A2)*24-C2/60)

If A2 is Monday at 10:00 PM and B2 is Tuesday at 6:00 AM, with C2 equal to 30, the result is 7.5 hours. Full timestamps also make it possible to represent a shift longer than 24 hours, though such a shift should be checked carefully. Entering the clock-out date correctly is essential: if both timestamps accidentally use the same date while the end time is earlier, the result will be negative.

For shifts with multiple unpaid breaks, either total the break minutes in one cell or calculate separate break durations in additional columns. Keep the input units and formula consistent. If workers clock in and out several times in one day, use a separate row for each work interval and add the interval results rather than trying to fit several pairs of times into one start/end pair.

Common errors and useful checks

  • A result shows as a time of day: The result cell may be formatted as a clock time. Use a decimal Number format for decimal hours, or [h]:mm for elapsed time.
  • The result is unexpectedly blank: The formula intentionally stays blank until both clock-in and clock-out cells contain values. Check that the entries are real times, not text copied from another system.
  • The result is negative or too large: Check whether the shift crossed midnight, whether the break was entered in minutes, and whether the start and end dates are correct. The time-only formula assumes no more than one midnight crossing.
  • A total appears to lose hours: Format a duration total as [h]:mm. A normal time format wraps after 24 hours.
  • There are tiny decimal differences: Excel stores time as fractions of a day, so decimal calculations can show small floating-point effects. Format the displayed result to a sensible number of decimal places; do not round every clock entry unless an established policy requires it.

Test the sheet with a few known cases before using it regularly: a daytime shift, a shift with a break, an overnight shift, and a blank row. Compare the answers with hand calculations. Protecting formula cells from accidental edits can also help; keep a separate copy of the original template so a changed formula can be restored.

When a spreadsheet is not enough

An Excel time card is useful for a small team, a personal record, or a simple schedule where employees enter a limited number of shifts. It is flexible, transparent, and easy to adapt. Its limitations are also practical: entries can be overwritten, employees may use inconsistent formats, and a workbook does not by itself verify who made an entry or preserve a reliable approval history.

If many people submit hours, managers need approval workflows, or records must be collected across locations, a dedicated time-tracking system may reduce manual work. Compare how it handles edits, approvals, exports, employee access, and data retention before choosing one. Excel remains useful for reviewing exported records, but the spreadsheet should not be treated as proof that a clock time is accurate merely because a formula calculated it correctly.

Frequently Asked Questions

What is the simplest formula to calculate hours worked in Excel?
With the start time in B2 and end time in C2, use =(C2-B2)*24 for a same-day shift with no unpaid break. For shifts that may pass midnight, use =MOD(C2-B2,1)*24. Add a break adjustment if the break is unpaid.

How do I subtract a 30-minute lunch break?
With clock-in in B2, clock-out in C2, and break minutes in D2, use =MOD(C2-B2,1)*24-D2/60. Enter 30 in D2 for a half-hour break. If the break is paid, do not subtract it.

How do I calculate hours for an overnight shift?
For a shift shorter than 24 hours with time-only entries, use =MOD(C2-B2,1)*24, subtracting any unpaid break as needed. If exact dates matter or shifts can exceed a day, record full date-and-time values and subtract those timestamps instead.

Why does my weekly total show the wrong time?
If you summed elapsed-time values, format the total as [h]:mm; a standard clock format wraps at 24 hours. If your daily results are decimal numbers, keep the total in a Number format instead.

Can Excel calculate overtime automatically?
Yes, formulas can split a weekly total around a threshold you specify. For example, =MAX(0,F2-40) calculates hours above 40 when F2 contains weekly decimal hours. The threshold is only an example; verify the applicable policy and rules before using it for payroll.

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.