Timesheet Formatting in Excel: Hours That Add Up
By Mark Fulton · 2026-09-21 · 10 min read
A timesheet in Excel adds up correctly when each column carries the right kind of time format. Start and end times are clock times (h:mm AM/PM), each day's hours are a duration (h:mm), and the weekly total uses the bracketed elapsed-time code [h]:mm, because Excel stores time as a fraction of a day and a plain h:mm total starts again at zero after every 24 hours. Shifts that cross midnight need =MOD(End-Start,1), and payroll gets a separate decimal column, =ROUND(Hours*24,2), so 8:30 becomes 8.50 rather than 8.3.
Search for a timesheet and you get downloads: weekly, biweekly, monthly, construction, attorney. Most of them work, right up until someone changes a formula, adds a sixth shift or pastes in hours from another file. Then the total says 4:30, or a cell fills with hash marks, and the template gives no clue why. The fix is almost never a new template. It is three format codes and one formula, and once you know what each does you can repair the timesheet you already have.
Why does Excel show 4:30 when the total is 28:30?
Because Excel does not store hours. It stores days. Microsoft's documentation for the TIME function puts it plainly: a time is a decimal from 0 to 0.99988426, covering 0:00:00 through 23:59:59. Noon is 0.5. Six hours is 0.25. A single day's shift is always a number below 1.
A week of shifts is not. Here is a part-time week, with the value Excel actually holds in each cell:
| Day | Start | End | Break | Hours shown | Stored value |
|---|---|---|---|---|---|
| Mon | 9:00 AM | 3:30 PM | 0:30 | 6:00 | 0.25 |
| Tue | 9:00 AM | 5:00 PM | 0:30 | 7:30 | 0.3125 |
| Wed | |||||
| Thu | 12:00 PM | 6:30 PM | 0:00 | 6:30 | 0.270833 |
| Fri | 10:00 PM | 7:00 AM | 0:30 | 8:30 | 0.354167 |
| Total | 4:30 | 1.1875 |
The sum is 1.1875, which is one full day plus 0.1875 of a day, and 0.1875 × 24 is 4.5 hours. The h:mm format is a clock face. It shows the time of day that 1.1875 lands on, which is 4:30 in the morning, and drops the whole day on the floor. Nothing is wrong with the arithmetic; the display is answering a different question.
The fix is a bracket. Microsoft's guide to adding and subtracting time uses the same example shape (12:45 plus 15:30 shows 4:15 instead of 28:15) and the same cure: select the total, open Format Cells (Ctrl+1, or Cmd+1 on a Mac), choose Custom, and type [h]:mm;@ in the Type box. Plain [h]:mm does the same job for a positive total. The square brackets tell Excel to keep counting hours past 23 instead of rolling over, so 1.1875 displays as 28:30.
That same Microsoft page notes that Excel for the web can add and subtract past 24 hours but cannot apply custom number formats. If your browser version has no Custom option, set [h]:mm once in the desktop app; the format travels with the file.
Which time format code should each column use?
One code per job, and the job is set by what the column means, not by what it happens to contain. The syntax behind these codes, including how sections and literals work, is in the Excel number formatting guide; this table is the timesheet subset.
| Column | Sample stored value | Format code | Displays | Use it for |
|---|---|---|---|---|
| Start / End, 12-hour | 0.916667 | h:mm AM/PM |
10:00 PM | Clock-in and clock-out |
| Start / End, 24-hour | 0.916667 | hh:mm |
22:00 | Rosters and shift work |
| Daily hours | 0.354167 | h:mm |
8:30 | One shift, always under 24 hours |
| Weekly total, wrong | 1.1875 | h:mm |
4:30 | Never on a total |
| Weekly total, right | 1.1875 | [h]:mm |
28:30 | Any sum of hours |
| Total in minutes | 1.1875 | [mm] |
1710 | Billing in minutes |
| Overnight shift | =MOD(0.291667-0.916667,1) = 0.375 |
h:mm |
9:00 | End time earlier than start |
| Decimal hours | =ROUND(1.1875*24,2) = 28.5 |
0.00 |
28.50 | Payroll and invoicing |
| Negative time | -0.625 | any time code | ##### | A formula that needs fixing |
Two details catch people. First, m means minutes only when it sits right after an h or right before an s; anywhere else, Microsoft's date and time format reference notes that Excel reads it as the month. A code like m "min" on its own will show a month number. Second, apply [h]:mm to the daily hours column too if anyone might ever enter a single shift over 24 hours, such as an on-call weekend. It costs nothing on normal rows.
Label the units in the header, not the cell: Hours (h:mm) and Hours (decimal). The reader should never have to guess whether 8.30 means eight and a half hours or eight hours and thirty minutes.
How do you handle a shift that crosses midnight?
Wrap the subtraction in MOD. A night shift from 10:00 PM to 7:00 AM gives End - Start of 0.291667 minus 0.916667, which is -0.625. In Excel's default 1900 date system, a negative time cannot be displayed, and Microsoft's page on the ##### error lists negative dates and times as one of its causes. The cell fills with hash marks however wide you make the column.
The formula that fixes it:
=MOD(C2-B2,1)-D2
where B is Start, C is End and D is the unpaid break. MOD returns a remainder with the same sign as the divisor, so dividing by 1 (one day) always gives a positive result. For a normal day shift nothing changes: 3:30 PM minus 9:00 AM is 0.270833, and MOD of that is the same 0.270833. For the night shift, MOD(-0.625,1) is 0.375, which is 9:00, and taking away the 0:30 break leaves 8:30. That is the Friday row in the table above.
Blank days should stay blank rather than show 0:00, so the full daily formula is:
=IF(B2="","",MOD(C2-B2,1)-D2)
SUM skips the empty text, so Wednesday's day off does not disturb the total.
Know the limit: MOD assumes no single shift reaches 24 hours. A 24-hour on-call block returns 0:00. If your team works shifts that long, record the start and end as full date and times (m/d/yyyy h:mm AM/PM), subtract them directly, and format the result as [h]:mm. The other source of hash marks is a break longer than the shift, which is a typo worth catching with validation (see below) rather than a formula.
How do you convert hours to decimal for payroll?
Multiply by 24, because the stored value is in days. Microsoft's own time-difference examples do the same conversion when they multiply a time difference by 24 for hours and by 1440 for minutes. Then round it:
=IF(E2="","",ROUND(E2*24,2))
Format that column as 0.00, not as a time. The Friday shift, 0.354167 days, becomes 8.50 hours. The weekly [h]:mm total of 28:30 becomes 28.50.
The ROUND is not decoration. Times are stored as binary fractions, so E2*24 can come back as something like 8.4999999 instead of 8.5. It displays correctly, but multiply it by a pay rate and compare it to a payroll export, and cents start disagreeing. Rounding each row to two places before anything else touches it keeps the decimal column and the pay column honest.
The mistake to guard against runs the other way: someone types hours as 8.15 meaning 8 hours 15 minutes. As a decimal, 8.15 hours is 8 hours and 9 minutes (0.15 × 60 = 9). The quarter-hours worth memorising:
| Clock | Decimal |
|---|---|
| 0:15 | 0.25 |
| 0:30 | 0.50 |
| 0:45 | 0.75 |
| 0:20 | 0.33 |
| 0:40 | 0.67 |
Pay goes in its own column: =IF(F2="","",F2*$H$1) with the hourly rate in one labelled cell (H1 here), formatted as currency. If you owe overtime past a weekly threshold, keep the threshold in a cell too and compute =MAX(0,F8-$H$2) on the decimal total. A number typed into the middle of a formula is the one nobody finds when the rule changes.
What should be locked so the person filling it in can't break it?
Everything except the four input columns. Microsoft's guide to locking specific areas of a protected worksheet explains the part that surprises people: every cell is already marked Locked by default, and the setting does nothing until the sheet is protected. So the steps run backwards from what you expect:
- Select the input cells only (Date, Start, End, Break for each row).
- Open Format Cells → Protection and clear Locked.
- Go to Review → Protect Sheet, and add a password if the sheet leaves your hands.
Now the formulas in Hours, Decimal and Pay, the rate cell and the total row cannot be typed over, which is how most homemade timesheets die: someone overwrites a formula with the number they think it should be, and it never recalculates again.
Add data validation on the input cells while the sheet is still unprotected. Data → Data Validation, Allow Time, between 0:00 and 23:59, stops text like "9am" or "nine" from entering a time column. A second rule on Break, Allow Time, less than or equal to 4:00 (or whatever your longest real break is), stops the typo that produces negative hours. The same constrain-the-input logic applied to a status column is in project tracker formatting.
How should the weekly total be presented?
As one clearly separated row, showing the total both ways. Under the last day, add a Total row with =SUM(E2:E6) formatted [h]:mm and =SUM(F2:F6) formatted 0.00. Give it a single top border and bold text; skip the double underline and the fill. If the two totals ever disagree (28:30 against anything other than 28.50), a row has been typed over, and you know before payroll does.
Keep the total row at the bottom of the week, not floating in a corner, and keep one week per block. A few more habits make it read cleanly:
- Right-align all time and number columns so the colons and decimal points line up.
- Put the employee name, week-ending date and rate above the table, not inside it. Header fields are read once; table rows are read many times.
- Keep the hourly rate out of the totals row. Summing a rate gives a number that means nothing.
- Freeze the header row if the sheet runs to a month, so "Hours (decimal)" stays visible at row 30.
Presentation choices for the rest of the sheet, such as widths, gridlines and one accent colour, are covered in how to make an Excel spreadsheet look professional.
Frequently asked questions
How do I sum hours over 24 in Excel?
Use =SUM() as normal, then format the total cell with the custom code [h]:mm. Open Format Cells with Ctrl+1 (Cmd+1 on a Mac), choose Custom and type the code into the Type box. Without the brackets, Excel shows the time of day the total lands on, so 28:30 appears as 4:30.
How do I calculate hours between two times?
Subtract the start from the end: =C2-B2, formatted as h:mm. If a shift can cross midnight, use =MOD(C2-B2,1) instead, which returns the right duration whether or not the end time is earlier than the start. Subtract an unpaid break at the end: =MOD(C2-B2,1)-D2.
Why does my timesheet show a negative time?
It shows ##### rather than a minus sign, because Excel's default 1900 date system cannot display negative times. The usual causes are an overnight shift calculated as End - Start (fix it with MOD) or a break entered longer than the shift (fix it with data validation on the Break column). Widening the column will not help.
Should timesheets use decimal hours or hh:mm?
Both, in separate columns. People enter and check hours as clock time, so the input and daily hours stay in h:mm. Payroll, invoices and anything multiplied by a rate need decimal hours, so add a =ROUND(Hours*24,2) column formatted 0.00. Showing both totals side by side also gives you a free consistency check.
Try it on your own timesheet
Once the time columns carry the codes above, drop the timesheet into the Excel formatter for the rest of the job. It adds a live SUM totals row on the decimal hours and pay columns while leaving the rate column out of it, gives currency its proper number format, freezes the header and cleans up header text, and lists each change so you approve it before anything is written. The file is read and rewritten in your browser, and your original stays as it was. The formatter reads clock-time cells as dates, so if a Start, End or Hours (h:mm) column comes back showing dates, reapply the h:mm and [h]:mm codes from the table above to it. The decimal hours and pay columns, the ones payroll reads, are where it does the most.