Spreadsheet Practice

How to calculate the hours between two times in Excel

Formula (Excel and Google Sheets)

=(C2-B2)*24

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.

Result on the example
one result per row in D2, filled down to D7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2, filled down to D7. Change any input and the result updates, or edit the formula to experiment.

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.

=(C2-B2)*24

Result8.5

  1. C25:00 PM

    The value in C2.

  2. B28:30 AM

    The value in B2.

  3. C2-B20.3541666667

    The - operator subtracts.

  4. C2-B2)*248.5

    The * operator multiplies.

In Google Sheets

The same formula works in Google Sheets: =(C2-B2)*24. We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=C2-B20.35, 0.32, 0.39…Different resultThe raw difference. Format the cell as h:mm to show it as 8:30. A fraction of a day.
=TEXT(C2-B2,"h:mm")8:30, 7:45, 9:20…Different resultHours 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate the hours between two times

In D2, calculate the hours between clock in and clock out as a decimal number, and fill down to D7.

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).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.