How to Calculate Time Difference in Google Sheets
Subtract the start time from the end time: =B2-A2. Format the result as duration to get hours and minutes between two times.
=B2-A2
A2 is the start time, B2 the end time. Then Format → Number → Duration so the result shows as h:mm, not a fraction.
How it works
Google Sheets stores a time as a fraction of a day, so subtracting one time from another gives the elapsed fraction. Formatting the cell as Duration turns that fraction into readable hours and minutes. To get a plain number of hours instead, multiply the difference by 24. When the end time is past midnight, add 1 to the end time so the result stays positive.
Variations
Total hours as a decimal
=(B2-A2)*24
Multiplying by 24 converts the day-fraction into hours, e.g. 1.5 for ninety minutes.
Total minutes
=(B2-A2)*1440
1440 minutes in a day; good for short intervals.
When the end time crosses midnight
=MOD(B2-A2,1)
MOD wraps the negative result back into a valid duration for overnight shifts.
Examples
| Scenario | Formula |
|---|---|
| Shift from 09:00 to 17:30 | =B2-A2 |
| Billable hours as a number | =(B2-A2)*24 |
FAQ
Why does my time difference show a decimal like 0.35?
The cell is formatted as a number. Select it and choose Format → Number → Duration, or multiply by 24 to get hours.
How do I handle times that go past midnight?
Use =MOD(B2-A2,1). MOD turns the otherwise-negative overnight result into a correct positive duration.
How do I add up a column of durations?
Use SUM, then format the total as Duration. For totals over 24 hours set the format to [h]:mm so hours don't reset.