Calculate the Difference Between Two Times in Excel

To calculate the time difference in Excel, subtract the start time from the end time, like =C2-B2. The result is a time, so 9:00 to 17:30 shows as 8:30. Multiply by 24 when you need decimal hours for pay or billing, and use MOD when a shift runs past midnight. This guide is for anyone who tracks shifts, call lengths or project hours and wants totals they can trust.

How Excel stores times#

Excel stores every time as a fraction of a day. Midnight is 0, 6:00 is 0.25, 12:00 is 0.5 and 18:00 is 0.75. One hour is 1/24 of a day, and one minute is 1/1440.

That is why time maths works with ordinary minus signs. 17:30 is 0.7292 and 9:00 is 0.375, so 17:30 minus 9:00 is 0.3542, which Excel shows as 8:30 when the cell has a time format. Dates work the same way, with whole numbers for days, which the date basics lesson explains. A date with a time is just both added together, so 1 March 2026 at 18:00 is the date number plus 0.75.

All the examples below use this shift list:

Subtract two times to get hours and minutes#

The basic time difference formula is end minus start:

=C2-B2

For Priya, the result is 8:30. If Excel shows a decimal such as 0.354 instead, the cell has the General format. Change it to a time format: press Ctrl+1 (Mac: ⌘1), choose Custom, and type h:mm.

Always put the end time first. =B2-C2 gives a negative time, which Excel shows as #####.

To take off a break, subtract it as well:

=C2-B2-D2

Priya worked 8:00 after her 30-minute break. Daniel worked 8:30 minus 0:45, which is 7:45.

Convert the difference to decimal hours or minutes#

Multiply the difference by the number of units in a day: 24 for hours, 1,440 for minutes and 86,400 for seconds.

Decimal hours are what you need for pay. If Priya earns 22 per hour, =(C2-B2)*24*22 gives 187. Multiplying the time 8:30 by 22 directly would give a wrong, tiny number, because 8:30 is really 0.354 of a day.

Format the decimal result as Number or General, not as a time. Omar's shift is a good test: 7:45 to 15:20 is 7 hours 35 minutes, which is 7.58 hours, not 7.35. Minutes are fractions of 60, not of 100.

To split the difference into parts, =HOUR(C2-B2) returns 8 and =MINUTE(C2-B2) returns 30 for Priya.

Night shifts: when the end time is after midnight#

When a shift ends the next day, end minus start is negative. Lena starts at 22:00 and ends at 6:00, so =C4-B4 is 0.25 - 0.9167, a negative number, and the cell fills with #####.

Use MOD to wrap the result around midnight:

=MOD(C4-B4,1)

MOD returns the remainder after dividing by 1, which turns -0.6667 into 0.3333. Formatted as h:mm, that is 8:00, the correct shift length. For normal day shifts, MOD changes nothing, so you can use it on every row.

Another common form adds one day when the end is earlier than the start:

=C4-B4+(C4<B4)

(C4<B4) is TRUE for Lena, which Excel counts as 1, so one full day is added. Both formulas give the same answer.

If your cells hold dates and times together, such as 1 March 2026 22:00 and 3 March 2026 7:15, you don't need MOD. Plain subtraction already knows which day each time is on. The result is 1 day and 9 hours 15 minutes, which is 33.25 hours. For differences of whole days only, the days between dates lesson covers DAYS and DATEDIF.

Totals over 24 hours: use [h]:mm#

A weekly total of shift times often goes over 24 hours, and the h:mm format then looks wrong. A week of 41 hours and 30 minutes shows as 17:30, because h:mm shows only the hours left after full days.

The fix is a custom format with square brackets:

1. Select the total cell, for example =SUM(E2:E6).
2. Press Ctrl+1 (Mac: ⌘1) and choose Custom.
3. Type [h]:mm and click OK.

The total now shows 41:30. Nothing about the value changed, only how it's shown. The guide to changing the date format explains more custom codes, and the timesheet walkthrough builds a full weekly sheet with these formulas.

Show a time difference as text#

TEXT turns a time into text in a format you choose, which is useful in labels:

="Shift length: "&TEXT(C2-B2,"h:mm")

This returns Shift length: 8:30. Use it only for display. TEXT returns text, so you can't add up the results with SUM. Keep a numeric column for totals and use TEXT in a separate label cell.

Common time difference mistakes#

These are the problems that most often give wrong hours:

1. Times stored as text. Times imported from another system may be text. They sit on the left of the cell, and maths gives #VALUE! or wrong totals. Convert them with VALUE(B2), as in the exercise on this page.
2. Multiplying a time by a rate. Convert to decimal hours first with *24.
3. Reading 7:35 as 7.35 hours. 35 minutes is 0.58 of an hour.
4. Forgetting night shifts. Use MOD if any end time could be after midnight.
5. A total that seems too small. Format it as [h]:mm.

The same thinking applies to dates: to move a date forward, see adding days, months and years, and for years of service, see calculating age and tenure. Teams that check supplier invoices and payment terms use these skills daily; the guide to Excel skills for accounts payable shows where.

Practise it#

Time maths makes sense once you've typed a few formulas yourself. Start with the exercise on this page, then practise date and time basics in a live spreadsheet: you type the formula and it checks it instantly. When you're ready, move on to the days between dates lesson to work with full dates and deadlines.