Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate elapsed time between two times in Excel and Google Sheets
Excel & Google Sheets
=B2-A2✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Start Time | End Time | Elapsed |
| 08:00 | 17:30 | |
| 09:15 | 11:45 | |
| 14:00 | 16:00 | |
| 07:30 | 09:00 |
=B2-A2→0.3958333333
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter the start time in A2 and the end time in B2, both formatted as Time.
- 2In a result cell, enter =B2-A2.
- 3Format the result cell as [h]:mm:ss (in Excel: Format Cells > Custom > [h]:mm:ss) to correctly show durations over 24 hours.
- 4In Google Sheets, use Format > Number > Duration or apply a custom format [h]:mm:ss.
- 5If the end time is before the start time (overnight shift), add 1: =B2-A2+(B2<A2).
Tips
- The key difference between h:mm and [h]:mm is the square brackets: [h] shows total hours including those over 24, while h resets after 24 and shows only the hour-of-day portion.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
How do I calculate elapsed time when the shift crosses midnight?
Add a conditional: =B2-A2+(B2<A2). The (B2<A2) portion evaluates to 1 (TRUE) when the end time is less than the start time, adding 1 day (24 hours) to the result.
How do I convert the elapsed time to total minutes or hours?
Multiply the time result by 1440 for minutes or by 24 for hours: =(B2-A2)*1440 gives total minutes. Format the result as a Number, not a Time.