How To Calculate Hours Worked In Excel
Subtract the start time from the end time, wrap it around midnight, take off the break and multiply by 24. Here is every formula you need, with results you can check.
How Excel Stores Time
Excel keeps a time as a fraction of one day. Noon is 0.5, six in the morning is 0.25, and midnight is 0. That is why a plain subtraction gives a strange number until you format it.
A shift from 9:00 AM to 5:30 PM is 8.5 hours, which Excel stores as 0.354167 of a day. Multiply by 24 to turn the day fraction into hours.
This one fact explains most spreadsheet problems. Show a cell as time and you see hours and minutes. Show it as a number and you see the day fraction.
Set Up The Columns
Use one row per day. Put the start time in column B, the end time in column C and the unpaid break in minutes in column D. Column E holds the result.
| Column | Holds | Format |
|---|---|---|
| A | Date | Date |
| B | Start time | Time, h:mm AM/PM |
| C | End time | Time, h:mm AM/PM |
| D | Unpaid break in minutes | Number |
| E | Hours worked | Number, 2 decimals |
Type times with the colon, such as 9:00 or 17:30. Excel then reads them as times and not as text. A cell that stays left-aligned is usually text.
The Basic Hours Formula
This formula gives decimal hours for a day shift and takes the break off:
=(C2-B2)*24-D2/60With a start of 9:00 AM, an end of 5:30 PM and a 30 minute break, the result is 8.00. Fill the formula down the column for each day.
Night Shifts That Cross Midnight
The basic formula breaks when the end time is smaller than the start time. It returns a negative number, and Excel shows a row of hash signs. Wrap the subtraction in MOD to fix it:
=MOD(C2-B2,1)*24-D2/60MOD with a divisor of 1 keeps only the part after the decimal point, so a negative day fraction wraps around. A shift from 10:00 PM to 6:30 AM with a 30 minute break gives 8.00 hours. The same formula also works for day shifts, so you can use it everywhere.
Hours And Minutes Instead Of Decimals
If you want 8:00 and not 8.00, skip the multiplication and format the cell instead. Use this formula and set the cell format to h:mm:
=MOD(C2-B2,1)-D2/1440Dividing minutes by 1440 turns them into a day fraction, because a day has 1,440 minutes. Both parts of the formula are now in days, so the subtraction is consistent.
Adding Up The Week
Use SUM on the column of daily results. In the sample week the days are 8:00, 8:00, 8:30, 8:00, 6:30, and the total is 39:00.
=SUM(E2:E6)If your daily cells are formatted as time, the total needs square brackets around the hour part. Format the total cell as [h]:mm. Without the brackets, Excel resets to zero every 24 hours, so 41:30 shows as 17:30.
The same total is 39.00 as a decimal. Set the cell to a number with two decimals if payroll wants that form.
Working Out Pay And Overtime
Multiply decimal hours by the hourly rate. If the rate is in cell G1 and the week total is in E7, pay is simple:
=E7*G1| Job | Formula |
|---|---|
| Overtime hours over 40 | =MAX(0,E7-40) |
| Regular hours up to 40 | =MIN(40,E7) |
| Gross pay with time and a half | =MIN(40,E7)*G1+MAX(0,E7-40)*G1*1.5 |
| Round to the nearest 15 minutes | =MROUND(C2,"0:15") |
| Round down to 15 minutes | =FLOOR(C2,"0:15") |
For US overtime, pay the first 40 hours at the base rate and the rest at time and a half. This formula does both in one cell:
The rules behind these numbers are on the overtime calculator page.
Google Sheets Differences
The formulas above work in Google Sheets without changes. Sheets also treats a time as a fraction of a day.
One difference is the format menu. In Sheets use Format, then Number, then Custom number format, and type [h]:mm for totals over 24 hours.
MROUND needs a number in Sheets and not a text string. Use =MROUND(C2,1/96) to round to 15 minutes, because a quarter hour is 1/96 of a day.
Common Problems And Fixes
- A row of hash signs means a negative time. Use the MOD version of the formula.
- A time that will not calculate is probably text. Retype it with a colon, or use TIMEVALUE.
- Totals that reset after 24 hours need the [h]:mm format.
- Results like 0.35 when you expect 8.5 mean you forgot to multiply by 24.
- Rounding every row, then adding, can drift. Add the exact values and round the total.
Questions People Ask
How do I calculate hours worked in Excel?
Subtract the start from the end, wrap it with MOD(...,1), multiply by 24 and take off the break. For 9:00 AM to 5:30 PM with a 30 minute break the answer is 8.00.
How do I calculate hours worked past midnight in Excel?
Use =MOD(C2-B2,1)*24. For 10:00 PM to 6:30 AM it returns 8.50 before the break comes off.
How do I show total hours over 24 in Excel?
Format the total cell as [h]:mm. The square brackets stop Excel from wrapping back to zero after 24 hours.
How do I take a lunch break off in Excel?
Store the break in minutes in its own column. Divide it by 60 for decimal hours or by 1440 for a time value, then subtract it.
Does the work hours formula also work in Google Sheets?
Yes. The same formulas work in Google Sheets. Only the custom number format menu and MROUND differ, as the Google Sheets section above explains.
Is there a free timesheet template?
Yes. The printable timesheets page has weekly, biweekly and monthly sheets in PDF and Excel with these formulas already set up.
Written and checked by the countmyworkhours.com team. How we calculate