How to subtract time in Excel and calculate hours worked

In short: =C2-B2 subtracts the start time from the end time. =(C2-B2)*24 turns the result into decimal hours, the number format [h]:mm shows totals above 24 hours, and =MOD(C2-B2,1) handles night shifts.

Try it now

The sheet lists start, end and break for six people. Work out the hours for the first row. Your formula is then filled down to the rest.

Task In E2, calculate the hours worked: from start to end, minus the break. The formula is then filled down to E7 and must be right in every row. Column E is already formatted as time.

	A	B	C	D	E
1	Name	Start	End	Break	Hours worked
2	Jana Weber	8:00	16:30	0:30
3	Murat Kaya	7:30	15:45	0:45
4	Lena Hoffmann	9:00	17:15	0:30
5	Tom Schulz	6:00	14:30	0:30
6	Sara Klein	10:00	19:00	1:00
7	Ali Demir	8:15	12:45	0:00

How Excel stores time

Excel treats one day as the number 1. A time is the part of that day that has passed: 12:00 is 0.5, 6:00 is 0.25 and 8:30 is 0.354166… The h:mm you see in the cell is just a number format on top. That is why the Excel formula for time is plain arithmetic: subtracting one time from another gives the duration between them.

Type times with a colon, like 8:30 or 4:30 PM. If the value lines up on the right of the cell, Excel recognised it as a number. Left aligned usually means text, and no formula will calculate with it.

Goal	Formula	Result in the example
Time on site	=C2-B2	16:30 minus 8:00 is 8:30
Hours worked minus break	=C2-B2-D2	minus 0:30 is 8:00
Break entered in minutes	=C2-B2-D2/1440	30 minutes divided by 1440 minutes per day
Decimal hours	=(C2-B2-D2)*24	8

Decimal hours for pay

As soon as hours feed into another calculation, such as pay, you need them as a decimal. To Excel, 7:30 is 0.3125, so multiplying it by an hourly rate gives nonsense. Multiply by 24 first and you get 7.5 hours. Set that result cell to General or Number, otherwise Excel may show 7.5 as a time again.

Task B2:B6 holds one week of hours worked, stored as time. In E2, calculate the weekly pay: all hours added up, times the hourly rate in E1.

	A	B	C	D	E
1	Day	Hours worked		Hourly rate $	18.5
2	Monday	6:30		Pay $
3	Tuesday	7:15
4	Wednesday	8:00
5	Thursday	5:45
6	Friday	7:30

Watch out when you convert in your head: 7.5 hours is 7 hours 30 minutes, not 7:50. To go back from decimal hours to a time, divide by 24: =E2/24.

Totals above 24 hours with [h]:mm

Add up a week with SUM and a 35 hour total may show as 11:00. The calculation is right. The standard time format simply rolls over to 0 after every 24 hours. A custom number format fixes the display:

1. Select the total and press Ctrl+1 to open Format Cells.
2. On the Number tab, pick Custom.
3. In the Type box enter [h]:mm. Microsoft's own guide uses [h]:mm;@, which displays the same way.

The square brackets tell Excel to keep counting hours past 24. Use [hh]:mm if you want two digit hours such as 08:00.

Night shifts: time across midnight

A shift from 22:00 to 6:00 ends on a smaller number than it starts, so =C2-B2 goes negative and the cell fills with hash signs. MOD with a divisor of 1 wraps any result into the range 0 to 1: =MOD(C2-B2,1) turns -0.6667 into 0.3333, which is 8:00. Same day shifts come through unchanged, so one formula covers the whole column.

Task The rota has early, late and night shifts. In D2, calculate how long each shift lasts, including shifts that end after midnight. Use MOD. The formula is filled down to D7.

	A	B	C	D
1	Name	Start	End	Duration
2	Jana Weber	22:00	6:00
3	Murat Kaya	6:00	14:30
4	Lena Hoffmann	21:30	5:15
5	Tom Schulz	14:00	22:15
6	Sara Klein	23:00	7:30
7	Ali Demir	18:00	2:00

With a break the formula becomes =MOD(C2-B2,1)-D2. Another option is to enter full date and time values, like 11/2/2026 22:00 and 11/3/2026 6:00, where a plain subtraction works. The date functions lesson covers how dates are stored.

TIME, HOUR and MINUTE

=TIME(8,30,0) builds a time from hours, minutes and seconds, handy for fixed values inside a formula such as a standard day: =E2-TIME(8,0,0). Going the other way, =HOUR(B2) and =MINUTE(B2) pull out the parts of a time, for example to flag anyone who started before 6 AM. HOUR only ever returns 0 to 23, so for weekly totals stick with *24.

Overtime against standard hours

With hours worked in E2 and the standard day in F2, overtime is =E2-F2. That breaks as soon as someone works less than the standard, because a negative time cannot be displayed in Excel's usual date system. Calculate overtime in decimal hours instead: =(E2-F2)*24 returns 0.5 or -0.5, and the column total is the balance for the month. To count the working days in that month, use NETWORKDAYS from the DATEDIF and NETWORKDAYS lesson.

Common mistakes

The total is too low

The total cell uses h:mm instead of [h]:mm, so it restarts after each 24 hours. Change the format and leave the formula as it is.

Pay comes out tiny

The time was multiplied by the rate directly. Without *24 you are paying per day, not per hour.

Hash signs instead of a result

Either the column is too narrow or the result is a negative time. Use MOD for night shifts and decimal hours for overtime.

Time formulas in German Excel

English	German
=MOD(C2-B2,1)	=REST(C2-B2;1)
TIME, HOUR, MINUTE	ZEIT, STUNDE, MINUTE
SUM	SUMME

German Excel separates arguments with semicolons and writes decimals with a comma, so 7.5 hours appears as 7,5. The format code [h]:mm is the same in both.

Which Excel versions support this?

Time arithmetic and the functions TIME, HOUR, MINUTE and MOD work in every current version of Excel, including Excel for Mac and Excel for the web. The custom [h]:mm format is set in the desktop app through Format Cells.

FAQ