For a basic Excel timesheet, put a shift’s start time in B2, end time in C2, and unpaid break length in hours in D2; calculate paid hours in E2 with =MOD(C2-B2,1)*24-D2. Then, if your overtime policy pays overtime after eight hours in a day, calculate daily overtime in F2 with =MAX(0,E2-8). The eight-hour threshold is only an example: overtime rules depend on the applicable law, workweek, and employer policy.
Set up a clear timesheet layout
Use one row per shift, with a heading in each column so employees and reviewers can tell what the numbers represent. A practical layout is:
- A: Work date
- B: Start time
- C: End time
- D: Unpaid break, entered as decimal hours
- E: Paid hours worked
- F: Overtime hours, if a daily overtime rule applies
Enter actual Excel time values in B and C, not text that merely looks like a time. For example, enter 9:00 AM as a time value and format the cells using Home > Number Format. Enter a half-hour unpaid break as 0.5 in D; enter a 45-minute break as 0.75. This keeps the break unit consistent with the decimal-hour result in E.
With the shift start in B2, end in C2, and unpaid break in D2 as decimal hours, the paid-hours formula for E2 is =MOD(C2-B2,1)*24-D2. Copy it down for the other shift rows. For a 9:00 AM to 6:00 PM shift with a 0.5-hour unpaid break, Excel returns 8.5 paid hours. The calculation is nine elapsed hours minus half an hour.
Calculate daily overtime when the rule is known
Overtime is not automatically the same thing as hours beyond eight in a day. The correct threshold and method depend on the rules that apply to the worker and the employer’s policy. Confirm those rules before building a payroll calculation; a spreadsheet can apply a rule consistently, but it cannot determine which rule governs a particular employee.
For an illustrative policy that counts hours above eight in each shift as daily overtime, with paid hours in E2, the overtime-hours formula in F2 is =MAX(0,E2-8). The regular-hours formula in G2 is =MIN(E2,8). For an 8.5-hour shift, F2 returns 0.5 and G2 returns 8. For a 7.5-hour shift, F2 returns 0 and G2 returns 7.5. These formulas split a shift at the stated threshold; they do not account for other possible rules, such as different thresholds or special treatment of particular hours.
If your policy uses a different daily threshold, replace 8 with that threshold in both formulas. Keep the threshold in a clearly labelled cell rather than burying it in a complicated formula if it may change. For example, you could put the applicable threshold in J1 and refer to J1 in the calculations. Document what the value means and who approved it, so a later reviewer does not mistake an example setting for a universal rule.
Handle overnight shifts and dated entries
The MOD function in the paid-hours formula allows a shift that begins before midnight and ends after midnight, as long as the shift is shorter than 24 hours and the cells contain times without dates. For example, a 10:00 PM start and a 6:00 AM end produce eight elapsed hours before subtracting any unpaid break. Without MOD, subtracting the earlier clock time from the later one can produce a negative result.
Times alone do not identify the date of each event. If you enter full date-and-time values in B2 and C2, the calculation can instead use the actual elapsed difference. With the complete start date and time in B2, complete end date and time in C2, and unpaid break hours in D2, the paid-hours formula in E2 is =(C2-B2)*24-D2. For a shift that starts Monday at 10:00 PM and ends Tuesday at 6:00 AM, the result is eight hours before breaks.
Use full dates when shifts may last 24 hours or longer, when a shift’s date matters for reporting, or when overnight work must be assigned to a specific workday. A times-only formula wraps around at midnight; it cannot tell whether an end time belongs to the next day or to a date several days later. For split shifts, use separate rows for separate work periods, or design a dated start-and-end structure that represents each interval explicitly.
Calculate overtime by workweek
Many overtime calculations use total hours in a defined workweek rather than a separate threshold for every day. The workweek’s start and end must be set correctly: summing an arbitrary seven-row block is only valid if those rows represent one complete workweek. Do not assume that a calendar week or pay period is necessarily the applicable workweek.
For a simple weekly example, assume E2:E8 contains paid hours for the seven days in one workweek and the illustrative overtime threshold is 40 hours. The weekly overtime-hours formula is =MAX(0,SUM(E2:E8)-40). If the seven entries total 42.5 hours, the formula returns 2.5 overtime hours. The corresponding regular-hours total, with the same layout and threshold, is =MIN(SUM(E2:E8),40), which returns 40 for that example.
This weekly example is not a complete payroll rules engine. If both daily and weekly overtime rules apply, the same hour might appear to exceed both thresholds; adding the two overtime totals can double-count it. The appropriate treatment depends on the governing rules and the employer’s approved method. Keep daily and weekly calculations separate until the rule for combining them is confirmed, and have the final design reviewed by a qualified payroll or compliance professional where needed.
Format hours so totals remain readable
The example formulas return decimal hours, which are convenient for multiplying by an hourly rate. Format E, F, and G as Number with an appropriate number of decimal places, such as two. In decimal hours, 8.5 means eight hours and thirty minutes; it does not mean eight hours and five minutes. This distinction is a frequent source of confusion when a decimal result is displayed beside clock times.
Excel also stores times and durations as fractions of a day. If you prefer to show elapsed time as hours and minutes, the result should be formatted as a duration rather than a clock time. A custom format such as [h]:mm allows accumulated hours to display above 24; a standard time-of-day display can wrap after 24 hours. Be consistent about units: do not subtract a decimal break value directly from a time serial, or multiply a decimal-hours total by 24 a second time.
For a weekly total of decimal hours in E2:E8, use a clear total label and a Number format. If instead you have stored time serials, use a duration format for the total. The same visual number can mean different things depending on whether a cell contains decimal hours or a fraction of a day, so label columns and formulas accordingly.
Estimate gross pay without confusing hours and rates
A timesheet can also estimate gross pay when regular hours, overtime hours, base hourly rate, and the applicable overtime multiplier are known. This is only an illustration of spreadsheet arithmetic, not advice about what rate or multiplier must be used. Check the actual employment terms and applicable requirements with a qualified professional.
Assume F2 contains regular hours, G2 contains overtime hours, H2 contains the base hourly rate, and I2 contains the approved overtime multiplier. The gross-pay formula in J2 is =F2*H2+G2*H2*I2. With 40 regular hours, 2.5 overtime hours, a $20 base rate, and an illustrative 1.5 multiplier, the result is $875: 40 × $20 is $800, and 2.5 × $20 × 1.5 is $75. Format J2 as Currency, and make sure the inputs are numeric rather than text.
Keep the multiplier in a separate, labelled cell when it may vary, and retain enough detail to explain how the hours were classified. A single gross-pay formula does not account for every payroll adjustment, premium, deduction, or jurisdiction-specific rule. Treat it as an estimate unless the workbook has been reviewed and configured for the intended payroll process.
Common formula problems and checks
If Excel shows a negative number or a string of hash marks, first check whether the cells contain valid time values and whether the result column is wide enough. A negative elapsed time often means the shift crossed midnight but the formula did not account for it. A zero or unexpectedly small result can indicate that a break was entered in minutes while the formula expects decimal hours, or that a time was stored as text.
Useful review checks include:
- Confirm each start and end time belongs to the correct work date.
- Check that the break is unpaid before subtracting it from paid hours.
- Verify that breaks are entered in decimal hours if the formula uses D2 as a decimal number.
- Compare a few rows with hand calculations, including an overnight shift and a shift with no break.
- Check weekly boundaries and make sure a week’s total includes the intended dates exactly once.
- Review formulas after copying them down so every row references its own start, end, and break cells.
Protecting formula columns from accidental edits can also reduce errors, but a protected sheet still needs a process for correcting mistakes. Keep an original copy of the workbook and use clear headings, date labels, and approval records if the sheet supports payroll decisions.
Choose a simple or more detailed timesheet
A one-row-per-shift layout is easy to understand and works well for regular shifts. A more detailed log may be preferable when employees take multiple breaks, work split shifts, or move between assignments with different rates. In that case, recording each work interval separately can preserve the detail needed to audit totals. Avoid forcing an elaborate set of rules into one formula if a reviewer cannot readily verify how it works.
Excel for Microsoft 365 on Windows uses the formulas shown here. The calculation functions also work in current Excel for Mac and Excel for the web, although ribbon details and some interactions can differ. For a small team, ordinary formulas and a clearly documented table may be enough. For larger or regulated payroll processes, use an approved timekeeping or payroll system where appropriate, and treat a spreadsheet as a supporting record rather than assuming a template guarantees compliance.
Frequently Asked Questions
How do I calculate hours worked in Excel with a lunch break? Subtract an unpaid break from the elapsed shift time. If the start and end are times in B2 and C2, and D2 contains the break in decimal hours, use the paid-hours formula shown above. A paid break should not be deducted as though it were unpaid.
How do I calculate overtime after 40 hours in Excel? Add the hours for the correct workweek, then subtract 40 only when the total exceeds that threshold. The example weekly formula returns 2.5 overtime hours for a 42.5-hour week. Confirm that 40 hours and the chosen workweek are appropriate for the situation.
Can Excel calculate an overnight shift? Yes. With times only and a shift shorter than 24 hours, the MOD-based formula handles an end time after midnight. For clearer date reporting or shifts of 24 hours or more, enter full start and end date-and-time values and use their actual difference.
Why does my Excel time total reset after 24 hours? A clock-time format displays time of day and can wrap after one day. Format accumulated durations with [h]:mm to show total hours beyond 24, or calculate in decimal hours and format the result as a number.
Should daily and weekly overtime formulas be added together? Not automatically. If both kinds of thresholds apply, hours can overlap, and summing separate overtime figures may count some hours twice. Confirm the applicable calculation method before combining totals.
Related
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFERROR With IF: How to Handle Formula ErrorsIn Excel, use IFERROR to return a chosen result when a calculation produces an error, and use IF…
- Excel Test Cell Value: Formulas for Common ChecksTo test whether a cell contains a particular value in Excel, use an IF formula such as =IF(A2="Paid","Match","No…
- NetSuite Saved Search Formula Examples and TipsNetSuite saved search formulas let you calculate, classify, format, and filter search results with SQL-style expressions, usually using…
- Excel Functions to Extract Text From Strings: A GuideExcel has several functions for extracting text from a string. Use LEFT, RIGHT, or MID to return characters…
- Excel Trim Decimal Places: Format or Change ValuesTo trim decimal places in Excel, select the cells and choose Home > Number > Decrease Decimal to…
- INDEX Excel Arguments Explained: Syntax and ExamplesIn Excel, INDEX returns a value or reference at the intersection of a specified row and column in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.