# Build a Timesheet in Excel: Overnight Shifts, Breaks, and Overtime That Actually Works

Time arithmetic in Excel breaks in three predictable places: shifts crossing midnight, decimal hours, and weekly overtime thresholds. Here is a timesheet that handles all three.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-timesheet-with-overtime
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 11 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## Build a Timesheet in Excel

Excel stores a time as a fraction of a day. Nine in the morning is 0.375, because it is three eighths of the way through. Everything that goes wrong with timesheets follows from that one fact, and everything that goes right follows from working with it rather than against it.

Tool: [Try the timesheet calculator](https://dothecalculation.com/calculators/timesheet-calculator) — Track weekly hours with custom daily and weekly overtime thresholds and export the result.

## Step 1: hours worked in a day

Columns: B for clock in, C for clock out, D for unpaid break in minutes, E for hours worked. Format B and C as time, and E as a plain number with two decimals.

**Hours worked, decimal**

```
=(C2 - B2) * 24 - D2/60
```
- C2 − B2 gives a fraction of a day. Multiplying by 24 turns it into hours.
- The break is in minutes, so divide by 60 to convert it into hours.
- 07:45 to 16:20 with a 30-minute break: 8.583 − 0.5 = 8.08 hours.

> **Why the result must be decimal** — Payroll multiplies hours by a rate, and 8 hours 30 minutes is 8.5, not 8.30. Leaving the cell formatted as time gives you 8:05, which cannot be multiplied by a pay rate correctly.

## Step 2: shifts that cross midnight

A shift from 22:00 to 06:00 gives a negative result with the simple formula, because 06:00 (0.25) minus 22:00 (0.9167) is negative. Excel displays this as a row of hash symbols.

**Midnight-safe hours**

```
=(MOD(C2 - B2, 1)) * 24 - D2/60
```
- MOD wraps a negative difference back into the 0 to 1 range.
- 22:00 to 06:00 with a 30-minute break: 8 − 0.5 = 7.5 hours.
- Use this version everywhere. It is correct for daytime shifts too.

The only case MOD cannot handle is a shift longer than 24 hours, which it would wrap incorrectly. If that is possible in your operation, store full date-and-time values rather than times alone and subtract those instead.

## Step 3: weekly overtime

Under the US Fair Labor Standards Act, non-exempt hours beyond 40 in a workweek are paid at 1.5 times the regular rate. Overtime is assessed per workweek and cannot be averaged across two weeks, which is why a 30-hour week followed by a 50-hour week still owes ten hours of premium.

**Splitting regular from overtime**

```
Regular: =MIN(E9, 40)    ·    Overtime: =MAX(0, E9 - 40)
```
- E9 is the weekly total, =SUM(E2:E8).
- MIN caps regular hours at the threshold; MAX floors overtime at zero.
- 46 hours becomes 40 regular and 6 overtime.

**Weekly pay**

```
=Regular * Rate + Overtime * Rate * 1.5
```
- 46 hours at $24: 40 × 24 + 6 × 24 × 1.5 = 960 + 216 = $1,176

## Step 4: states with a daily threshold

Several US states, California among them, apply a daily threshold as well: over 8 hours in a day at 1.5x, over 12 at double time. Where both a daily and a weekly rule apply, the employee gets whichever is more favourable, and the hours already paid at a premium daily are not counted again weekly.

**Per-day columns for a daily-threshold state**
| Column | Formula | Meaning |
| --- | --- | --- |
| F (Regular) | =MIN(E2, 8) | First 8 hours |
| G (Time and a half) | =MIN(MAX(0, E2-8), 4) | Hours 9 to 12 |
| H (Double time) | =MAX(0, E2-12) | Beyond 12 |

Sum each column for the week. Then apply the weekly rule only to regular hours above 40, so a day already paid at a premium is not double counted. If your workforce spans several states, drive the thresholds from a lookup table keyed on state rather than hard-coding 8 and 40, because the numbers differ and change.

## Step 5: rounding to the nearest interval

Many employers round clock times to the nearest six or fifteen minutes. Under US rules the rounding must be neutral over time, meaning it cannot systematically favour the employer.

**Rounding a clock time**

```
=MROUND(B2, "0:15")    ·    or =MROUND(B2, 15/1440)
```
- 1440 is the number of minutes in a day, so 15/1440 is a quarter hour as a fraction.
- MROUND rounds to nearest, which is the neutral direction.
- FLOOR always rounds down and is not neutral; do not use it on clock-in times.

## Step 6: showing hours in both formats

Payroll wants decimal hours, but people reading the sheet want to see 8:05. Show both, with the decimal as the value payroll uses.

**Decimal hours back to a time display**

```
=TEXT(E2/24, "[h]:mm")
```
- The square brackets around h stop the display resetting at 24 hours.
- Without them, 40 hours displays as 16:00.
- This is a text string, so do not do arithmetic on it.

> **The bracket detail matters** — For any total that can exceed a day, the custom format must be [h]:mm. A weekly total of 46 hours shows as 22:00 without the brackets, and the error looks plausible enough to reach payroll.

## Step 7: guard against bad entries

- Blank rows: wrap the hours formula in =IF(OR(B2="", C2=""), "", ...) so empty days show blank rather than zero or an error.
- Break longer than the shift: =IF(D2/60 > (MOD(C2-B2,1))*24, "Check break", ...) flags an impossible entry.
- Implausible shifts: conditional formatting on any day over 16 hours catches a mistyped AM or PM.
- Locked formula columns: protect the calculation columns so staff enter times only.

All of this works identically in Google Sheets. MOD, MIN, MAX, MROUND, and TEXT take the same arguments, and time is stored as the same fraction of a day.

Tool: [Try the overtime and shift differential calculator](https://dothecalculation.com/calculators/time-card-overtime-calculator) — Handle daily and weekly overtime, double time, and night or weekend differentials together.

**How do I calculate hours worked in Excel across midnight?**

Use =(MOD(C2-B2, 1))*24 rather than =(C2-B2)*24. MOD wraps the negative difference back into a valid range, so a 22:00 to 06:00 shift returns 8 hours instead of an error.

**How do I convert time to decimal hours in Excel?**

Multiply the time difference by 24 and format the cell as a number. 8 hours 30 minutes becomes 8.5, which is what payroll multiplies by the rate. Leaving it formatted as time gives 8:30, which cannot be multiplied correctly.

**What is the Excel formula for overtime over 40 hours?**

Regular hours are =MIN(total, 40) and overtime is =MAX(0, total-40). Pay is regular × rate + overtime × rate × 1.5. Assess it per workweek; overtime cannot be averaged across two weeks.

**Why does my weekly total show 22:00 instead of 46 hours?**

The cell format resets at 24 hours. Use the custom format [h]:mm, with the square brackets, so totals beyond a day display correctly.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-timesheet-with-overtime). Quote freely with attribution and a link to this page._