Developer ToolsFree generatorPDF, Excel, Word exportManufacturing

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

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.

Fill in

Start time cell

End time cell

Unpaid break minutes cell

Hours worked cell

Weekly hours range

Daily overtime limit (hours)

Weekly overtime limit (hours)

Hourly rate cell

Overtime multiplier

Generated code

Download:

Hours worked, including shifts across midnight

=ROUND((MOD(C2-B2,1)*24)-(D2/60),2)

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

Round to the nearest quarter hour

=MROUND(MOD(C2-B2,1)*24,0.25)-(D2/60)

Use only where your policy and local law permit rounding.

Daily overtime hours

=MAX(0,E2-8)

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

=MAX(0,SUM(E2:E8)-40)

Regular hours for the week

=SUM(E2:E8)-MAX(0,SUM(E2:E8)-40)

Gross pay for the week

=(SUM(E2:E8)-MAX(0,SUM(E2:E8)-40))*$H$1+MAX(0,SUM(E2:E8)-40)*$H$1*1.5

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.

Move timesheets out of Excel

Optional: a short conversation about connecting shop floor labor reporting to your ERP.

Optional. The tool and every export work without this. No spam.

How to use the formulas

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

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.

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

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

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.

Frequently Asked Questions

Why does my night shift show a negative number?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools

Related Tools

Safety & Workforce

More Developer Tools tools

See all

Back to All Tools