Excel Timesheet: How to Add Up Hours Correctly

To add up timesheet hours in Excel, put each shift’s worked duration in a cell and total the duration cells with =SUM(C2:C8). Format the total cell with the custom number format [h]:mm so the hours keep accumulating past 24 instead of starting over at zero.

Set Up a Simple Timesheet

A basic timesheet can use one row per workday, with columns for the date, start time, end time, break, and hours worked. For example, place dates in column A, start times in B, end times in C, and calculated durations in D. Put the weekly total below the duration entries. Keeping each day on its own row makes it easier to check entries and spot missing information.

Excel needs real time values to calculate elapsed time. Enter a time such as 8:30 AM or 17:15, then format the cell as a time if needed. You can choose a time format from Home > Number, or open Format Cells with Ctrl+1 on Windows. On Mac, use Command+1 to open Format Cells. Typing a time as text, such as an entry preceded by an apostrophe, may prevent Excel from treating it as a value that can be added or subtracted.

It also helps to distinguish between a time of day and a duration. A start time such as 9:00 AM is a point on the clock; a duration such as 7 hours and 30 minutes is elapsed time. Both are stored as fractions of a day in Excel, but a duration needs a format that displays elapsed hours correctly.

Calculate Each Day’s Hours

When a shift starts and ends on the same day, subtract the start time from the end time. With the start time in B2 and the end time in C2, enter =C2-B2 in D2. For a shift from 9:00 AM to 5:30 PM, the result is 8 hours and 30 minutes. Format D2 as a duration, such as [h]:mm, or as h:mm when you know individual shifts will always be shorter than 24 hours.

Copy the formula down column D for the remaining rows. In Microsoft 365 for Windows, select the formula cell and drag its fill handle down, or double-click the fill handle when adjacent rows contain data. Excel adjusts the row references as the formula is copied. Check that each row points to the correct start and end cells before relying on a weekly sum.

For a concrete example, suppose Monday’s start and end times are 8:00 AM and 4:30 PM, and Tuesday’s are 9:00 AM and 5:00 PM. The daily durations are 8:30 and 8:00, for a total of 16:30. The calculation is elapsed time; it does not automatically subtract an unpaid meal break or apply an overtime rule.

Add Up the Week Without Losing Hours

With durations in D2:D8, put the weekly total in D9 using =SUM(D2:D8). The formula adds the stored duration values. If the week contains 40 hours and 30 minutes, the underlying result is 1 day plus 16 hours and 30 minutes, because Excel represents one day as 1. A regular clock display may show only 16:30, which is why the total cell’s number format matters.

Select D9, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose Custom, and enter [h]:mm as the type. Square brackets around h tell Excel to show total elapsed hours rather than the hour on a 24-hour clock. The total will display as 40:30, not 16:30. The formula does not change when you change the display format; formatting affects how the result appears, not the calculation.

The brackets are especially important for a timesheet total that may exceed 24 hours. A format such as h:mm resets the displayed hour after each full day, while [h]:mm can show 40:30 or 72:15. Minutes still appear after the colon, so 40:30 means 40 hours and 30 minutes, not 40 minutes past a time of day.

Handle Shifts That Cross Midnight

A simple subtraction can produce a negative result if a shift begins before midnight and ends after it. For a shift from 10:00 PM to 6:00 AM, with the start in B2 and end in C2, use =MOD(C2-B2,1) in D2. The result is 8:00. MOD wraps the negative difference around one day, treating the end time as occurring on the following day.

This approach assumes a shift is shorter than 24 hours and that an end time earlier than the start means the shift continued into the next day. It cannot distinguish an overnight shift from a data-entry mistake, such as accidentally entering 6:00 AM instead of 6:00 PM. If a shift could last 24 hours or longer, or if you need a reliable record of the actual dates, enter full start and end date-times instead.

With the start date-time in B2 and the end date-time in C2, use =C2-B2 in D2. For a shift from 10:00 PM on Monday to 6:00 AM on Tuesday, enter both the dates and times; the result is 8 hours. This method records which calendar day the shift ended and avoids relying on the assumption that an earlier clock time always means the next day.

Subtract Unpaid Breaks

If the end and start times include an unpaid break, subtract that break from the elapsed shift. One convenient layout has the start time in B2, end time in C2, and the break length in minutes in D2; put the net worked duration in E2. Use =MOD(C2-B2,1)-D2/1440 in E2. Dividing minutes by 1,440 converts them into a fraction of a day, since a day contains 24 × 60 minutes.

For a 9:00 AM to 5:30 PM shift with a 30-minute unpaid break, enter 30 in D2. The elapsed shift is 8:30, the break is 0:30, and the calculated net duration is 8:00. Format E2 as [h]:mm if you want the duration shown as hours and minutes. If breaks are entered as actual Excel times rather than a number of minutes, the formula should subtract that time value directly instead; choose one consistent break-entry method for the whole sheet.

Do not subtract a break that is already excluded from the recorded working period. For example, if an employee enters a start time after a break or records separate work periods around the break, subtracting it again would undercount the hours. Decide what the start and end entries represent and label the columns clearly.

Show the Total as Decimal Hours

Some reports or payroll systems ask for decimal hours instead of hours and minutes. With a duration total in D9, enter =D9*24 in E9 to convert the Excel time value to decimal hours. For a total of 40:30, the result is 40.5 hours, because 30 minutes is half an hour. Format E9 as Number and choose the decimal places you need.

Keep the hours-and-minutes total available even if you also produce a decimal figure. The two formats express the same duration differently: 7:15 is 7.25 decimal hours, not 7.15. To convert minutes to a decimal fraction, divide the minutes by 60; reading the digits after a colon as hundredths gives the wrong answer.

Rounding is a separate decision from converting the total. Displaying two decimal places changes what is shown, but does not necessarily change the stored value used in later calculations. If a workplace applies a specific rounding policy, follow that policy and make its method explicit. Avoid rounding every daily duration unless that is intended, because small daily differences can accumulate across a week.

Common Problems and How to Fix Them

The total looks too small after 24 hours: Apply the custom format [h]:mm to the total cell. The sum may be correct even when a clock-style format hides complete days.
A formula displays as text or does not calculate: Check whether the cell is formatted as Text, whether the formula begins with an equals sign, and whether calculation is set to Automatic under Formulas > Calculation Options. If a cell was set to Text before the formula was entered, change its format and re-enter the formula.
The total is zero or unexpectedly low: Check that the entries are numeric Excel times, not text, and that the SUM range includes the intended duration cells. Blank cells are normally ignored by SUM, but a misspelled or incomplete range can omit workdays.
An overnight shift shows a negative value: Use the MOD calculation for shifts under 24 hours, or enter the full date and time for both endpoints and subtract them.
Hours appear to be missing from the daily figures: Check whether breaks have been omitted from, or subtracted twice from, the recorded shift. Also confirm that the start and end entries use the intended AM/PM values.

Excel may store time calculations with tiny binary precision differences. A value that should be exactly eight hours can occasionally have a very small internal fraction beyond or below that value. Usually, a time format hides this difference. If you are building calculations that compare durations exactly, consider whether a deliberate rounding rule is appropriate, rather than assuming the displayed minutes reveal every stored decimal.

Make the Timesheet Easier to Maintain

For a reusable sheet, use clear headings, keep formulas in a separate duration column, and format input and result cells consistently. You can turn the data range into an Excel table with Insert > Table. Tables can make a growing list easier to filter and can carry formulas into new rows, although a total row must still use a suitable elapsed-time format if it may exceed 24 hours.

Protecting formula cells can reduce accidental edits in a shared workbook, but protection is not a substitute for checking the numbers. Before sharing a timesheet, verify a normal shift, an overnight shift if relevant, a break deduction, and a total over 24 hours. A few deliberate test entries can reveal a format or reference error before real records are added.

Keep raw time entries separate from calculated values. This makes it easier to audit an unusual total: a reviewer can see what was entered, how a break was handled, and which formula calculated the duration. If several employees use the same workbook, agree on time-entry conventions, including whether users enter 24-hour time, AM/PM, full dates for overnight work, and breaks in minutes or as times.

Frequently Asked Questions

How do I total hours in Excel when the result is over 24 hours?
Use SUM on the duration cells, then format the result as [h]:mm. The brackets make Excel display accumulated hours rather than resetting the hour display after each day.

How do I calculate hours worked when a shift ends the next day?
For shifts shorter than 24 hours, use =MOD(C2-B2,1) when the start is in B2 and the end is in C2. If you need to record exact dates or a shift might last 24 hours or more, enter full date-times and subtract the start from the end.

How do I turn 7 hours and 30 minutes into a decimal?
Multiply the Excel duration by 24. A duration of 7:30 becomes 7.5 decimal hours. Thirty minutes is 30 divided by 60, or 0.5 hour.

Can Excel subtract a lunch break automatically?
Yes. Record the break consistently, then subtract it from the elapsed shift. If the break is entered as minutes, divide the minutes by 1,440 in the duration calculation; make sure the break has not already been excluded from the start and end times.

Why does my Excel timesheet show a negative number?
A same-day subtraction becomes negative when the end time is earlier than the start time, as with an overnight shift. Use a formula that accounts for midnight or enter the full dates as well as the times. Also check for an incorrectly entered AM/PM value.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.