Excel guide · 4 min read

Calculate hours between two dates and times in Excel

Q: How do I calculate hours between two dates and times in Excel?

Subtract the start from the end and multiply by 24: =(C2-B2)*24. Both cells must hold a date and a time. Wrap it in ROUND(...,2) to get a clean figure like 50.58.

Q: How do I get the minutes between two date-times?

Multiply the difference by 1440: =ROUND((C2-B2)*1440,0). Use 86400 for seconds.

Q: Why does my time difference reset after 24 hours?

The cell is formatted h:mm, which shows only the hours after whole days. Change the custom format to [h]:mm, or show decimal hours with *24.

Q: How do I show the gap as days and hours?

Use =INT(C4-B4)&" days "&TEXT(C4-B4,"h:mm"). For T-1043 that gives "2 days 2:35". It returns text, so keep a numeric hours column for totals.

Q: How do I exclude weekends from the hours?

Use NETWORKDAYS for the whole days and MEDIAN to clamp the first and last day to your working hours, as in the business hours section above. Add your holiday list as the third NETWORKDAYS argument.

Quick answerWhen each cell holds a full date and time, subtract and multiply by 24: =(C2-B2)*24 gives the hours between them, and *1440 gives minutes. Because the date is part of the value, gaps that cross midnight or run for several days work with no extra fix. To show the gap as hours and minutes, format it as [h]:mm, not h:mm.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	F	G	H	I	K
1	Ticket	Opened	Closed	SLA (h)	Open date	Open time	Close date	Close time	Raw export
2	T-1041	Oct 26, 2026 9:12	Oct 26, 2026 15:40	8	Oct 27, 2026	11:30	Oct 29, 2026	14:05	2026-10-27T11:30:00
3	T-1042	Oct 26, 2026 16:55	Oct 27, 2026 10:20	24					2026-10-29T14:05:00
4	T-1043	Oct 27, 2026 11:30	Oct 29, 2026 14:05	48
5	T-1044	Oct 28, 2026 22:10	Oct 29, 2026 1:45	4
6	T-1045	Oct 30, 2026 15:20	Nov 2, 2026 10:45	24
7	T-1046	Nov 2, 2026 8:05	Nov 2, 2026 8:52	4

Download this sheet as an Excel practice file (.xlsx)

Subtract, then multiply by 24

Excel stores a date-time as one number: the whole part counts days and the decimal part is the time of day. 26 Oct 2026 at 18:00 is the date's serial number plus 0.75. So end minus start gives the gap in days, and multiplying by 24 turns days into hours.

The ticket log above has an Opened and a Closed timestamp for each support ticket. T-1043 was opened on Tuesday at 11:30 and closed on Thursday at 14:05. Wrap the result in ROUND, because time arithmetic leaves tiny floating-point tails like 6.4666666668.

=ROUND((C4-B4)*24,2)

Result: 50.58

50.58 hours: two full days (48 hours) plus 2 hours 35 minutes.

Show it as hours and minutes: [h]:mm

For a readable 50:35, leave the plain subtraction =C4-B4 in the cell and format it with Home > Number Format > More Number Formats > Custom, type [h]:mm. The square brackets tell Excel to keep counting hours past 24.

Without the brackets, h:mm shows only the hours left over after whole days. The value underneath is still right, which is what makes this trap so easy to miss: the cell looks like a normal duration.

=TEXT(C4-B4,"h:mm")

Result: 2:35

2:35, which hides the 48 hours of whole days. =TEXT(C4-B4,"[h]:mm") shows the true 50:35.

Minutes instead of hours

Multiply by 1440, the number of minutes in a day. This is the version to use for short gaps such as response times. T-1046 was picked up at 8:05 and closed at 8:52.

Always round minute results. Without ROUND this one comes out as 46.999999996, and a test like =(C7-B7)*1440>=47 would say FALSE even though the gap is 47 minutes.

=ROUND((C7-B7)*1440,0)

Result: 47

47 minutes. For seconds, multiply by 86400 instead.

Date and time in separate columns

Many exports split the date and the time into two columns, like F1:I2. Add them back together first: a date plus a time is a full date-time. Then subtract as usual.

If you subtract only the times (I2-G2), you get 2:35 and lose the two days in between. If you subtract only the dates, you get whole days. Always combine both parts on each side.

=ROUND(((H2+I2)-(F2+G2))*24,2)

Result: 50.58

50.58 hours, the same as the single-column version for T-1043.

Check each ticket against its SLA

With the hours in hand, compare them with the target in column D. =IF((C5-B5)*24>D5,"Breached","Met") flags one row. To count the breaches for the whole log in one cell, compare all rows at once inside SUMPRODUCT.

Note that T-1044 runs from 22:10 to 1:45 the next day. Because both cells carry the date, no MOD trick is needed: 3.58 hours, inside its 4-hour SLA.

=SUMPRODUCT(--((C2:C7-B2:B7)*24>D2:D7))

Result: 2

2 breaches: T-1043 (50.58 h against 48) and T-1045 (67.42 h against 24).

Business hours only (9:00 to 17:00, Monday to Friday)

T-1045 looks terrible at 67 elapsed hours, but it was opened late on a Friday and closed on Monday morning. If your SLA counts only working hours, you need NETWORKDAYS for the whole days and MEDIAN to clamp the first and last day into 9:00-17:00.

How it works: (NETWORKDAYS-1) times 8 hours counts the working days between, the MEDIAN on the end time gives how far into the last day the ticket closed, and the MEDIAN on the start time takes off the part of the first day before it opened. Add a holiday range as the third argument of each NETWORKDAYS to skip public holidays. This assumes the same hours every working day; for split shifts or different weekends (NETWORKDAYS.INTL lets you set those), a helper table of daily hours is easier to check.

=ROUND(((NETWORKDAYS(B6,C6)-1)*8/24+IF(NETWORKDAYS(C6,C6),MEDIAN(MOD(C6,1),9/24,17/24),17/24)-MEDIAN(NETWORKDAYS(B6,B6)*MOD(B6,1),9/24,17/24))*24,2)

Result: 3.42

3.42 working hours: 1:40 on Friday afternoon plus 1:45 on Monday morning. The same formula gives 18.58 for T-1043.

Common mistakes

Using h:mm for a gap longer than a day

It drops whole days and shows only the leftover hours. Use [h]:mm, or multiply by 24 for decimal hours.

The timestamps are text, often ISO style like 2026-10-27T11:30:00

Excel does not read the T, so subtraction gives #VALUE!. Convert with =VALUE(SUBSTITUTE(K2,"T"," ")) and subtract the converted values: here that gives 50.58 hours for K2 to K3.

The result shows #####

The end is earlier than the start, so the gap is negative, and Excel cannot show a negative time. Check that the columns are not swapped, or use the decimal *24 version, which shows the negative number.

Comparing an unrounded result with a target

47 minutes can come out as 46.999999996. ROUND the hours or minutes before testing them with >, < or =.

FAQ

How do I calculate hours between two dates and times in Excel?

Subtract the start from the end and multiply by 24: =(C2-B2)*24. Both cells must hold a date and a time. Wrap it in ROUND(...,2) to get a clean figure like 50.58.

How do I get the minutes between two date-times?

Multiply the difference by 1440: =ROUND((C2-B2)*1440,0). Use 86400 for seconds.

Why does my time difference reset after 24 hours?

The cell is formatted h:mm, which shows only the hours after whole days. Change the custom format to [h]:mm, or show decimal hours with *24.

How do I show the gap as days and hours?

Use =INT(C4-B4)&" days "&TEXT(C4-B4,"h:mm"). For T-1043 that gives "2 days 2:35". It returns text, so keep a numeric hours column for totals.

How do I exclude weekends from the hours?

Use NETWORKDAYS for the whole days and MEDIAN to clamp the first and last day to your working hours, as in the business hours section above. Add your holiday list as the third NETWORKDAYS argument.

Functions used

Related guides

Updated November 3, 2026