=B2-A2 returns the time between a start time in A2 and an end time in B2. Excel stores a time as a fraction of a day (12:00 is 0.5), so the result is a fraction too: format it as h:mm to read 8:30, or multiply by 24 to get 8.5 hours.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | Hours | Decimal hours |
| 2 | 9:00 | 17:30 | 8:30 | 8.50 |
| 3 | 8:15 | 16:45 | 8:30 | 8.50 |
| 4 | 7:30 | 12:00 | 4:30 | 4.50 |
| 5 | 13:00 | 18:20 | 5:20 | 5.33 |
The first row is 8:30 as a time and 8.50 as a number. 13:00 to 18:20 is 5:20, or 5.33 hours. Use the h:mm column when people read the result and the decimal column when it feeds a calculation such as pay. In a cell formatted as General, the same result shows as the raw fraction, 0.354167 for 8:30.
Add or subtract hours and minutes
Adding a time to a time works the same way. TIME(hours, minutes, seconds) builds the amount to add, or divide by 24 for hours and by 1440 for minutes.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | Plus 1:30 | Plus 45 minutes | Minus 2 hours |
| 2 | 9:00 | 10:30 | 9:45 | 7:00 |
| 3 | 14:20 | 15:50 | 15:05 | 12:20 |
From 9:00 the three columns give 10:30, 9:45 and 7:00; from 14:20 they give 15:50, 15:05 and 12:20. A time can also be typed in quotes: =A2+"1:30" adds an hour and a half.
Calculate time over midnight
A night shift from 22:00 to 6:00 ends "before" it starts, so =B2-A2 is negative: -16 hours. A time cannot be negative, and Excel fills the cell with #####. MOD(B2-A2,1) adds one day to a negative result and leaves a positive one alone, so it works for day and night shifts in the same column.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | Hours | Decimal hours |
| 2 | 22:00 | 6:00 | 8:00 | 8.00 |
| 3 | 9:00 | 17:00 | 8:00 | 8.00 |
| 4 | 23:30 | 7:15 | 7:45 | 7.75 |
The three shifts are 8:00, 8:00 and 7:45 long. Another way to write it is =B2-A2+(B2<A2): the comparison is 1 when the end time is earlier, which adds a day. When start and end are full date-times (2026-03-14 22:00), plain subtraction is enough, because the dates already say which day each time is on.
Sum hours over 24 hours
h:mm shows a time of day, so it wraps around at 24: a week of 42 hours 30 minutes is shown as 18:30. The format [h]:mm, with square brackets, shows total hours without wrapping.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | Hours | Total as [h]:mm |
| 2 | 8:00 | 16:30 | 8:30 | |
| 3 | 8:00 | 16:30 | 8:30 | |
| 4 | 9:00 | 17:30 | 8:30 | |
| 5 | 8:00 | 16:30 | 8:30 | |
| 6 | 8:00 | 16:30 | 8:30 | |
| 7 | Total | 18:30 | 42:30 |
C7 shows 18:30 and D7 shows 42:30. Both cells hold the same number (1.77 days); only the format differs. To set it: Home > Number Format > More Number Formats > Custom, and type [h]:mm. For the total as a decimal, multiply by 24: 42.5.
Convert time to decimal hours and back
Multiply a time by 24 for hours, by 1440 for minutes. To go back, divide decimal hours by 24 and format the cell as h:mm. HOUR and MINUTE return the parts of a time as whole numbers.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Time | Hours | Minutes | HOUR | MINUTE | Back from hours |
| 2 | 6:45 | 6.75 | 405 | 6 | 45 | 6:45 |
| 3 | 1:20 | 1.33 | 80 | 1 | 20 | 1:20 |
| 4 | 0:50 | 0.83 | 50 | 0 | 50 | 0:50 |
6:45 is 6.75 hours, 405 minutes, hour 6 and minute 45. 1:20 is 1.33 hours. HOUR returns 0 to 23 only, so for a duration of more than a day use =INT(A2*24) instead. Column F turns the decimal hours back into the original times. If =A2*24 shows 18:00 in your workbook, the result took the time format of A2: set the cell to General or Number.
Practice: pay and night shifts
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | Rate | Pay |
| 2 | 8:30 | 16:00 | 18 |
Your turn: In D2, calculate the pay: the hours between the start in A2 and the end in B2, times the hourly rate in C2.
| A | B | C | |
|---|---|---|---|
| 1 | Start | End | Hours |
| 2 | 22:00 | 5:30 | |
| 3 | 9:00 | 17:00 |
Your turn: In C2, return the length of the shift as decimal hours, then fill it down to C3. A shift may run past midnight.
Why a time result shows ##### or a decimal
Three display problems account for most time questions:
#####: the result is a negative time, usually an overnight shift calculated with=B2-A2. Use=MOD(B2-A2,1). (A column that is too narrow shows#####too; widen it to tell the two apart.)- A decimal like 0.354: the cell is formatted as General. Apply
h:mm, or multiply by 24 if you wanted hours. - A total that is too small: the sum passed 24 hours and
h:mmwrapped. Use[h]:mm.
Times typed as text ('9:00, or values pasted from another system) are not numbers: SUM skips them, so a total of such cells comes out too small or 0. =ISNUMBER(A2) returns TRUE for a real time; =TIMEVALUE(A2) converts text such as "9:00".
Frequently Asked Questions
How do I calculate the hours between two times in Excel?
Subtract the start from the end, =B2-A2, and format the cell as h:mm. For the hours as a number, multiply by 24: =(B2-A2)*24 turns 8:30 into 8.5.
How do I calculate time over midnight in Excel?
Use =MOD(B2-A2,1). A shift from 22:00 to 6:00 gives -16 hours with plain subtraction; MOD adds a day to any negative result, which gives 8:00.
Why does my total of hours reset after 24 hours?
The h:mm format shows the time of day, so 42 hours 30 minutes is shown as 18:30. Format the total as [h]:mm (Home > Number Format > More Number Formats > Custom) to show 42:30.
How do I convert time to decimal hours in Excel?
Multiply by 24: =A2*24. Excel stores a time as a fraction of a day, so 6:45 is 0.28125 and times 24 is 6.75 hours. Multiply by 1440 for minutes.
How do I add minutes to a time in Excel?
=A2+TIME(0,45,0) adds 45 minutes. =A2+45/1440 does the same, since a day has 1440 minutes. TIME also takes hours: TIME(2,30,0) is two and a half hours.
