Table of Contents
To calculate the time difference between two times in Google Sheets, you can subtract one time from the other. This will give you the difference in terms of hours, minutes, and seconds. You can also add the TIME function to get the difference in decimal hours, minutes, and seconds. You can also use the DATEDIF function to calculate the exact number of days, months, and years between two dates.
You can use the following basic syntax to calculate a time difference between two times in Google Sheets:
=B1-A1
This particular formula assumes the starting time is in cell A1 and the ending time is in cell B1.
In order for this formula to work properly, the cells in A1 and B1 must both have a Date time format.
The following example shows how to use this formula in practice.
Example: Calculating a Time Difference in Google Sheets
Suppose we have the following two columns in Google Sheets that show the start and end time of some events:
Before calculating the time difference for each event, we must make sure that the times are in a Date time format.
To do so, highlight the cells in the range A2:B5 and then click the Format tab along the top ribbon, then click Number, then click Date time:
Each of the times will automatically be converted to a Date time format:
Next, we can simply subtract the start times from the end times:
To convert the time difference to a time format, highlight the cells in the range C2:C5, then click the Format tab along the top ribbon, then click Number, then click Duration.
The time differences will automatically be converted into a Hours: Minutes: Seconds format.
If you’d like to extract the individual hours, minutes, and seconds from the time difference, you can use the HOUR, MINUTE, and SECOND functions:
