You can use the following basic syntax to calculate a time difference between two times in Google Sheets:
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:
The following tutorials explain how to perform other common tasks in Google Sheets: