# Excel Timesheet Formula Generator: Hours Across Midnight, Daily and Weekly Overtime

> Source: https://netray.co/tools/excel-timesheet-overtime-formula-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Creates ready-to-paste Excel formulas for night-shift hours, unpaid breaks, rounding, daily and weekly overtime, and gross pay.

## Category

developer

## Industry

Manufacturing

## Audience

- Plant HR
- Production supervisor
- Payroll clerk

## Tool

**Kind:** template

**Output:** code

**Language:** text

**Fields:**

#### Start time cell

**Key:** start_cell

**Type:** text

**Default:** B2

#### End time cell

**Key:** end_cell

**Type:** text

**Default:** C2

#### Unpaid break minutes cell

**Key:** break_cell

**Type:** text

**Default:** D2

#### Hours worked cell

**Key:** hours_cell

**Type:** text

**Default:** E2

#### Weekly hours range

**Key:** week_range

**Type:** text

**Default:** E2:E8

#### Daily overtime limit (hours)

**Key:** daily_limit

**Type:** number

**Default:** 8

#### Weekly overtime limit (hours)

**Key:** weekly_limit

**Type:** number

**Default:** 40

#### Hourly rate cell

**Key:** rate_cell

**Type:** text

**Default:** $H$1

#### Overtime multiplier

**Key:** multiplier

**Type:** number

**Default:** 1.5

**Blocks:**

#### Hours worked, including shifts across midnight

**Body:** =ROUND((MOD({{end_cell}}-{{start_cell}},1)*24)-({{break_cell}}/60),2)

Example: start 22:00, end 06:00, break 30 minutes gives 7.50 hours.

#### Round to the nearest quarter hour

**Body:** =MROUND(MOD({{end_cell}}-{{start_cell}},1)*24,0.25)-({{break_cell}}/60)

Use only where your policy and local law permit rounding.

#### Daily overtime hours

**Body:** =MAX(0,{{hours_cell}}-{{daily_limit}})

#### Weekly overtime hours (total of the week above the limit)

**Body:** =MAX(0,SUM({{week_range}})-{{weekly_limit}})

#### Regular hours for the week

**Body:** =SUM({{week_range}})-MAX(0,SUM({{week_range}})-{{weekly_limit}})

#### Gross pay for the week

**Body:** =(SUM({{week_range}})-MAX(0,SUM({{week_range}})-{{weekly_limit}}))*{{rate_cell}}+MAX(0,SUM({{week_range}})-{{weekly_limit}})*{{rate_cell}}*{{multiplier}}

**Notes:** Format start and end cells as time (hh:mm) and the hours cell as a number. Where a place counts both daily and weekly overtime, do not simply add the two results, because the same hour can be counted twice. Check local law and your agreement before using these for payroll.

## Content

**Intro:** Many plants still keep timesheets in Excel, and the hard parts are night shifts that cross midnight, unpaid breaks and overtime. This generator writes the formulas for your own cell references so you can paste them straight in.

**Sections:**

#### How to use the formulas

**Body:** Enter the cell references where your start time, end time, break minutes and hours worked live, then set the overtime limits and pay rate cell. Copy each formula into the first row of your sheet and fill down. Format the time columns as hh:mm so Excel stores them as fractions of a day. The weekly formulas expect a range of daily hours for one employee and one week.

#### Why MOD handles shifts across midnight

**Body:** Excel stores a time as a fraction of a day, so 22:00 is 0.9167 and 06:00 is 0.25. Subtracting start from end gives a negative number for a night shift. MOD with a divisor of 1 wraps the result into a positive day fraction, so MOD(0.25-0.9167,1) returns 0.3333, which is 8 hours when multiplied by 24. Break minutes are then divided by 60 and subtracted.

**Bullets:**

- Multiply by 24 to convert a day fraction to hours
- ROUND keeps floating point noise out of totals
- MROUND rounds to a chosen increment such as 0.25

#### Daily and weekly overtime

**Body:** Daily overtime is simply hours above the daily limit. Weekly overtime is total hours above the weekly limit. In jurisdictions that apply both, such as California, overtime is the larger of the two interpretations on a per hour basis, not the sum, so a more elaborate sheet is needed. For the common weekly-only rule, the formulas here are enough, and the pay formula applies the multiplier only to the overtime hours.

#### Common mistakes

**Body:** Typical errors include typing times as text, forgetting the break in the hours cell, summing hours formatted as time (which resets after 24 hours unless you use the custom format [h]:mm), and applying the multiplier to the whole week. Also avoid rounding every punch when your policy only rounds the total.

**Cta:** If timesheets are still in Excel while your ERP holds the work orders, we can help connect labor data so supervisors stop re-keying hours.

**Faq:**

#### Why does my night shift show a negative number?

**Answer:** Because the end time is smaller than the start time on the same date. Wrap the subtraction in MOD with a divisor of 1, as in the first formula, or store full date and time values in the cells so the subtraction works without wrapping.

#### How do I sum more than 24 hours in Excel?

**Answer:** Format the total cell with the custom format [h]:mm so hours do not roll over after 24. If you already converted to decimal hours by multiplying by 24, the sum is a normal number and needs no special format.

#### Can I use the weekly overtime formula for biweekly pay?

**Answer:** Only if the law or agreement counts overtime over a two week period, which is rare. In most places overtime is measured per week even with biweekly pay. Use one weekly range for each week and add the results.

#### Does this work in Google Sheets?

**Answer:** Yes. MOD, ROUND, MAX, SUM and MROUND all exist in Google Sheets with the same syntax. Cell references and time formats work the same, though the custom number format dialog is in a different menu.

## Search terms

excel timesheet formula overnight shift, excel overtime formula over 40 hours, excel hours worked across midnight, timesheet formula deduct break, excel daily overtime over 8 hours, excel mround quarter hour timesheet, excel weekly overtime calculation, shift hours formula excel

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json