Excel Timesheet Formula: How to Calculate Time Worked

A Friday payroll file should not depend on one manager remembering whether 7:45 means seven hours or seven forty-five. The right excel timesheet formula turns clock-in and clock-out times into hours you can sum, approve, and export—without re-keying every shift by hand. This guide shows the formula to calculate time worked in Excel for hourly teams: setup, breaks, decimal payroll hours, overnight shifts, and weekly totals.

Who this guide is for: HR coordinators, ops managers, and small-business owners building or fixing a spreadsheet timesheet for US shift crews—not students doing a one-off homework sheet. If you only need a finished grid, download our timesheet Excel template (formulas included). For what a timesheet document is and how approval works, see the timesheet glossary; for capture policy, see employee time tracking.

In this guide: table layout, time formats, core formulas, lunch breaks, decimal and 24-hour time, midnight crossings, pay-period sums, a simple overtime flag, troubleshooting, Google Sheets notes, and when to move to time tracking software.

Disclaimer: General payroll orientation only—not legal or tax advice. Federal, state, and local wage rules vary; confirm rounding and overtime with qualified counsel.

Who this Excel timesheet formula guide is for

Spreadsheets work when you have one site, a handful of employees, and a manager who audits every row before payroll. They break down when version control fails—someone emails an old file, a formula gets overwritten, or crews round hours differently than payroll.

This article assumes you are building a weekly or pay-period grid for non-exempt or hourly staff in hospitality, retail, or healthcare. Salaried exempt employees may not need daily formulas, but many employers still collect project or PTO hours in the same layout.

Most Excel how-tos online target students or solo freelancers—not payroll approvers on a Friday deadline. Here the math is employer-first: the same totals you should see in a timesheet calculator or your payroll import after managers sign off.

Set up a timesheet table in Excel

Before you write any excel timesheet formula, decide whether each row is a shift or a calendar day. Restaurants and clinics often need one row per shift when someone works a split or double. Retail stores with single eight-hour blocks can use one row per day. Mixing both styles in one file without labeling rows is how payroll ends up with duplicate hours.

Start with one row per shift (or per day if each person works one shift). Typical columns:

Freeze the header row and name the sheet by pay period end date so managers approve the right week. Before you share the file, use the lock steps below so punch columns stay editable but formulas do not.

Monthly vs weekly layouts

A weekly timesheet fits most hourly payroll cycles: seven rows per employee plus a total row. A monthly timesheet in Excel is usually a grid with dates across the top and employees down the side—same formulas in every day cell, but harder to audit. If you need a month on one page, start from our timesheet Excel template instead of merging random weekly files at month-end.

Add a hidden “version” cell (for example v3) and a “pay period ending” cell managers must fill before anyone enters hours. That small habit prevents approving last month’s tab after a holiday weekend.

Lock formulas and share the file safely

Before you email a workbook to site managers, turn on Review → Protect Sheet (or Protect Workbook for structure). Unlock only the time-entry columns so formulas in the hours column cannot be deleted by accident. If you use SharePoint or OneDrive, turn on version history so payroll can open last Tuesday’s file when someone insists “the total changed.”

Name files with pay period end dates (Timesheet_PP2026-09-15.xlsx) instead of “final FINAL v2”—your future self and your auditor will thank you.

Format cells for time in Excel

Excel stores times as fractions of a day. If you type 9:00 AM and 5:00 PM in cells formatted as Time, subtraction works. If Excel shows large decimals, re-apply time format (Home → Number → Time).

Enter times consistently. Mixing 9:00 without AM/PM on a 12-hour sheet causes silent errors. For overnight work, see the midnight section below—format alone will not fix a backwards subtraction.

Data validation for time entry

On the time-in and time-out columns, use Data Validation → Time so crews cannot type free text like “around 9”. For decimal-hour entry only (no clock times), validate Decimal between 0 and 24 on the hours column and hide the clock columns—some union or agency formats work that way, but most shift employers prefer real punch times for audits.

If managers paste values from a POS or scheduling export, use Paste Special → Values so you do not overwrite formulas in the hours column. One accidental paste is enough to break an entire pay period.

Weekly display: decimals vs [h]:mm totals

Payroll usually wants decimal hours in column F, so the pay-period total is simply =SUM(F2:F8). Managers sometimes want to see durations as 7:30 instead of 7.50—format the duration column (not the decimal column) with [h]:mm after subtracting times without multiplying by 24.

Do not apply [h]:mm to a column that already holds decimals from *24; Excel will show nonsense. Pick one column for payroll export (decimals) and optionally one for crew-readable durations, both driven by the same in/out/break cells.

Basic Excel timesheet formula: hours worked (end minus start)

The core formula to calculate time worked in Excel subtracts start from end:

=C2-B2

where B2 is time in and C2 is time out. Format the result as time or as [h]:mm if the shift might exceed 24 hours in a single cell (rare for one shift, common for weekly sums).

To express the same duration as decimal hours for payroll (seven hours thirty minutes = 7.50):

=(C2-B2)*24

Microsoft documents elapsed-time subtraction in its Add or subtract time in Excel support article—useful when training managers who are new to duration math.

Double-check with our time card calculator on a few rows before you trust an entire pay period.

Step-by-step: how to calculate hours worked in Excel

To calculate hours worked in Excel for payroll, run through this checklist every time you build or audit a row:

1. Format time in and time out as Time (not General or Text).
2. Enter unpaid break as a duration in column D (for example 0:30 for thirty minutes) or as lunch out/in in two columns.
3. In the hours column, use =(C2-B2-D2)*24 when D holds break duration.
4. Confirm the sample row by hand: 9:00 AM to 5:15 PM minus 0:30 break = 7.75 decimal hours (matches the table above for A. Lee on Mon 9/8).
5. Copy the formula down only after that row matches your policy and your payroll import test.

That five-step check catches most “Excel is wrong” tickets before they reach HR—usually the issue is text-formatted times or a missing *24, not the concept.

One net-hours formula for column F

When column B is time in, C is time out, and D is unpaid break duration, put this in F2 and fill down:

=IFERROR(ROUND((C2-B2-D2)*24,2),"")

ROUND(...,2) keeps payroll-friendly hundredths; drop ROUND if your provider wants full precision. For overnight rows, use =(MOD(C2-B2,1)-D2)*24 only after you validate one night shift manually—do not mix MOD and non-MOD rows without labeling them.

IFERROR and blank rows

Wrap the first data row so blank lines do not show errors in payroll exports:

=IFERROR((C2-B2)*24,"")

That keeps SUM ranges clean when a new hire has not worked yet. Do not use IFERROR to hide real negative durations—fix the underlying start/end instead.

Subtract lunch and unpaid breaks

An excel timesheet formula with lunch break is the same end-minus-start math—you subtract unpaid break time before you convert to decimals. When break duration sits in its own column (D2), subtract it from gross shift time:

=(C2-B2-D2)*24

If break is stored as start/end times (lunch out and lunch in), use the same subtraction pattern on the break columns, then subtract break hours from work hours:

=(C2-B2)*24-(E2-D2)*24

ExcelJet’s basic timesheet formula with breaks uses MOD when breaks cross midnight—helpful for night crews:

=MOD(C6-B6,1)-MOD(E6-D6,1)

Only unpaid breaks come out of paid hours. Paid rest breaks depend on state law and your handbook—do not subtract them here without policy approval.

Document break rules in the same workbook on a “Policy” tab: minimum break length, paid vs unpaid, and whether auto-deduct applies when someone forgets to punch lunch. Managers should not rely on memory when the spreadsheet already encodes the math.

Auto-deduct lunch when crews forget to punch

Some employers auto-deduct thirty minutes when a shift exceeds six hours and break cells are blank. You can encode that policy—not legal advice, only math—with a helper column:

=(C2-B2-IF(D2="",IF((C2-B2)*24>6,TIME(0,30,0),0),D2))*24

Here D holds break duration as a Time value (for example 0:30). Blank break on shifts longer than six hours deducts thirty minutes; otherwise blank means no break entered. Simpler approach: require managers to fill break before approval and reject empty D. Either way, write the rule on the Policy tab so payroll and crews know what the sheet does.

Decimal hours for payroll

Many US payroll imports expect decimal hours, not 7:30 text. Common conversions:

Formula approach: =ROUND((C2-B2-D2)*24,2) if you round to hundredths. Align rounding with written policy—some employers round punches to the nearest quarter hour before payroll.

You can also keep a duration column in [h]:mm for managers and a decimal column for payroll export. Label both clearly so nobody submits the wrong column to your provider.

Payroll systems differ on whether they want two decimal places or three. Test one approved week in a sandbox import before you reformat two years of history. If your provider expects minutes as integers instead of decimals, add a helper column =ROUND(F2*60,0) only after counsel or payroll confirms that format.

Some handbooks round punches to the nearest quarter hour before payroll math. You can mirror that in Excel with =MROUND((C2-B2-D2)*24,0.25)—but only if written policy says quarter-hour rounding; rounding and overtime interact with state rules, so document what the sheet does on your Policy tab.

Keep this reference table on a “Formulas” tab or print it for managers who do not live in Excel every day:

Night shifts that cross midnight

When time out is “earlier” on the clock than time in (10:00 PM start, 6:00 AM end), plain C2-B2 can show a negative value. Fixes:

• MOD method: =MOD(C2-B2,1) treats the difference as a duration within one day cycle
• Date + time: store full date-time in B and C, then =(C2-B2)*24

For multi-day pay periods, always include the shift date column so overnight hours attach to the correct workday for state daily overtime rules—Excel will not infer that for you.

When several crew members work the same overnight pattern, copy the MOD formula from a single “golden row” you validated manually. Night-shift errors cluster by site—if one line cook’s hours look wrong, check the whole closing team before payroll cutoff.

Worked example: J. Patel in the sample table (10:00 PM–6:00 AM, 0:30 break) should net 7.50 decimal hours. If Excel shows a negative or a fraction near zero, switch that row to the MOD pattern or store full date-time values so the end time falls on the next calendar day.

Weekly totals and pay-period sums

After hours worked populate per row, sum by employee or by week:

=SUM(F2:F8)

For multiple employees on one sheet, use SUMIF:

=SUMIF(A:A,"A. Lee",F:F)

Match the sum range to your pay period boundaries. Biweekly payroll still benefits from weekly subtotals—managers catch missing days before you close the period.

Export the decimal column as CSV only after approval; keep a read-only copy of the signed period for wage-and-hour recordkeeping (see FAQs). Under the federal Fair Labor Standards Act, covered employers must keep accurate time records for non-exempt workers—Excel can hold those records when the file is controlled, but it is not tamper-evident. Treat the approved export plus signature block as your audit trail for that pay period.

Build a simple approval block at the bottom: manager name, date signed, and “totals match schedule Y/N”. That mirrors what digital employee time tracking tools do in software—Excel can still capture the attestation if you are not ready to buy a system.

Flag overtime hours in Excel

Federal overtime rules are nuanced; this section only shows spreadsheet math, not legal tests. If weekly hours over 40 should be flagged for review:

=IF(SUM(F2:F8)>40,SUM(F2:F8)-40,0)

Place that next to the employee’s weekly total. Daily overtime states need a different structure—often one row per day with a threshold column. For pay outcomes, see overtime pay and use our overtime calculator when you need rate examples.

Spreadsheet flags are reminders, not legal determinations. Exempt roles, fluctuating workweeks, and industry exemptions still belong in HR policy—not in a single IF statement you found online.

Daily overtime flags (spreadsheet math only)

States with daily overtime thresholds need one row per shift (or per day), not only a 40-hour week check. A review column next to decimal hours might look like:

=IF(F2>8,F2-8,0)

That flags hours above eight in a single day for manual review—it does not calculate premium pay, blended rates, or exemptions. Pair flags with counsel-approved payroll rules; use the overtime pay glossary and your payroll provider’s rules for actual dollars owed.

Fix common Excel timesheet errors

When a row fails, compare against a known-good shift in the timesheet calculator before you copy the formula down two hundred rows.

Turn on Show Formulas (Ctrl+` on Windows) before you email a file to payroll—managers catch overwritten cells faster when they see the logic instead of a wall of decimals.

Partial shifts, edits, and “split” rows

When someone leaves early or picks up a second shift the same day, use one row per shift instead of editing time out twice on a single line. Two rows sum cleanly in SUM; one row with manually edited in/out times often hides a deleted break or double-counted lunch.

If a manager must correct a punch after the fact, change the time cells and add a note column (“corrected 9/10 per supervisor”) rather than typing over the formula result. Payroll should always import the formula column, not hard-typed decimals that no longer match the clock times.

Google Sheets differences

Google Sheets uses the same timesheet math as Excel: =(C2-B2-D2)*24, MOD for midnight, and SUM for weekly totals. What changes is formatting, sharing, and export—not the underlying formulas.

Shared Google Sheets can solve version drift for small teams, but permission settings matter. Without protected ranges, a well-meaning shift lead can delete a column you spent an hour debugging. For multi-site employers, Sheets is still one shared document—same scaling limits as Excel on email attachments, just with better live collaboration.

If your team already lives in Sheets, copy the column layout from our timesheet Excel template tab by tab; once in/out cells are formatted as time, the same formulas calculate hours worked without rewriting logic.

When a spreadsheet stops scaling

Excel timesheets fail quietly: wrong version, broken macro, no mobile clock-in on a busy floor, no audit trail when a manager edits a cell. Signs it is time to upgrade:

• More than one site or approver editing the same file
• Missed punches discovered after payroll submits
• You need schedule vs actual comparison every week
• State audits ask for tamper-evident history you cannot produce from email attachments

Next steps in order: use our timesheet Excel template with locked formulas, validate math with calculators, then evaluate time tracking software and Ordio time tracking when schedules, approvals, and exports should live in one system.

Ordio helps shift operators connect approved hours with scheduling and employee scheduling so payroll does not depend on a single fragile workbook.

Save a PDF snapshot of each approved period alongside the live file. If someone later edits a formula, you can still show auditors what managers signed when payroll ran—the same discipline you expect from a digital time and attendance export, even while you are still on Excel.

Quick recap: One row per shift, Time-formatted punches, net hours with =(C2-B2-D2)*24, midnight fixes with MOD or date-time, weekly SUM aligned to your pay period, and a spot-check with the timesheet calculator before you roll formulas out to the full roster.