# How to calculate hours between two dates and times in Excel

Subtract the two date-times and multiply by 24: `=(C2-B2)*24`. It works across midnight and across several days.

**Excel:** `=(C2-B2)*24` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Ticket | Opened | Resolved | Hours |
| T-4401 | 46083.385416666664 | 46083.697916666664 |  |
| T-4402 | 46083.583333333336 | 46085.4375 |  |
| T-4403 | 46084.92361111111 | 46085.069444444445 |  |
| T-4404 | 46086.333333333336 | 46090.333333333336 |  |
| T-4405 | 46087.72222222222 | 46088.37847222222 |  |

Formula in D2, filled to D6: `=(C2-B2)*24`

Result: 7.50; 44.50; 3.50; 96.00; 15.75

## How it works

A date-time is a date number plus a time fraction, so subtracting two of them gives days with a fraction, even across midnight or several days. Multiplying by 24 gives hours: March 2 at 2:00 PM to March 4 at 10:30 AM is 1.85 days, or 44.5 hours.

## Other ways

- `=ROUND((C2-B2)*24,2)`: Rounded to two decimals, which removes floating-point noise.
- `=INT(C2-B2)&" days "&TEXT(C2-B2,"h:mm")`: Days plus hours and minutes, like 1 days 20:30. (Text in days and h:mm.)

## Common mistakes

- If the dates and times are in separate columns, add each pair first: `=((D2+E2)-(B2+C2))*24`.
- Text timestamps can't be subtracted. Convert them with DATEVALUE and TIMEVALUE.

## Questions

**How do I count business hours only?**

Use NETWORKDAYS with MEDIAN to trim each end to opening hours; see the working-hours formula.

**How do I show the result as days, hours and minutes?**

`=INT(C2-B2)&"d "&TEXT(C2-B2,"h""h ""m""m""")` gives 1d 20h 30m.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/hours-between-two-dates-and-times