# How to calculate the hours between two times in Excel

Subtract the start from the end and multiply by 24: `=(C2-B2)*24`. Times are fractions of a day, so the 24 turns the difference into hours.

**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 |
| --- | --- | --- | --- |
| Employee | Clock in | Clock out | Hours |
| Ava | 0.3541666666666667 | 0.7083333333333334 |  |
| Ben | 0.3229166666666667 | 0.6458333333333334 |  |
| Cara | 0.375 | 0.7638888888888888 |  |
| Dev | 0.25 | 0.6145833333333334 |  |
| Eli | 0.5208333333333334 | 0.875 |  |
| Fay | 0.4236111111111111 | 0.6527777777777778 |  |

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

Result: 8.50; 7.75; 9.33; 8.75; 8.50; 5.50

## How it works

Times are stored as fractions of a day: 6:00 AM is 0.25 and noon is 0.5. Subtracting two times gives the fraction of a day between them, and multiplying by 24 turns it into hours: 8:30 AM to 5:00 PM is 8.5 hours.

## Other ways

- `=C2-B2`: The raw difference. Format the cell as h:mm to show it as 8:30. (A fraction of a day.)
- `=TEXT(C2-B2,"h:mm")`: Hours and minutes as text, like 9:20. (Text in h:mm form.)

## Common mistakes

- If the result shows as a time like 12:00 PM, the cell inherited a time format. Format it as a number.
- A clock-out after midnight gives a negative difference. Use `MOD(C2-B2,1)*24` for night shifts.
- Times stored as text can't be subtracted. Convert them with TIMEVALUE first.

## Questions

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

Leave out *24 and format the cell as `h:mm`: `=C2-B2`.

**How do I round to the nearest quarter hour?**

`=MROUND((C2-B2)*24,0.25)`.

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-times