Format cells that contain times as a specific time or duration with Format > Number > Time or Duration. You can then use the standard + and – operators to add and subtract times in calculations.
Do you want to add, subtract or find the difference (duration) between two beats? If so, Google Sheets makes it super easy to do just that—no features required. We’ll show you how.
Add time in google sheets
To add hours, minutes, or seconds to your specified time in Google Sheets, use the standard “+” (plus) operator.
Start by opening your web browser, launching Google Sheets, and accessing your spreadsheet. We will use the following sheet as an example:
In this spreadsheet, we will use the following cells:
- B2: This cell contains the start time. We will add our time to this time.
- C2: This is where we will specify the time to add.
- D2: This will display the resulting time.
Since both B2 and D2 will display real time, we’ll format them to use the time format. To do so, we’ll select those cells and choose Format > Number > Time from the menu bar.
We will then select cell C2 and choose Format > Number > Duration. This is because this cell will display a time period and not the time itself.
We will add 5 hours, 54 minutes and 28 seconds to the specified time. To do so, we’ll click on cell C2 and enter
In cell D2, where we want to display the resulting time, we will enter the following formula and press Enter.
This formula adds the time period in cell C2 to the time in cell B2. You will see the resulting time in cell D2.
And everything is ready.
If you want to include dates in your calculation, select your cells and choose Format > Number > Date and Time from the menu bar.
Subtract time in Google spreadsheets
To subtract time in Google Sheets, use the standard “-” (minus) operator.
Start by launching your spreadsheet in Google Sheets. We will use the following sheet as an example:
In this spreadsheet, we will use these cells:
- B2: This cell has the time from which you will subtract a time period.
- C2: This is where you will define the time period to subtract.
- D2: This cell will display the resulting time.
We’ll select cells B2 and D2 and make them use the time format by choosing Format > Number > Time from the menu bar.
We will choose Format > Number > Duration for cell C2. In cell C2, we will specify the time period to subtract from cell B2. As an example, we will use 8 hours by entering
In cell D2, we will enter the following formula and press Enter:
The resulting time will appear in cell D2.
And that’s the resulting time after subtracting the specified time period from the source time.
Find the difference between two specified times
To find the difference between two hours (for example, how many hours are there between 9 am and 6 pm), use the following method.
We will use the following spreadsheet as an example:
In this spreadsheet, we have used the following cells:
- B2: This cell shows the start time.
- C2: This cell shows the end time.
- D2: This cell will display the real time difference between the start and end times.
Since cells B2 and C2 will display actual times, we’ll format them by choosing Format > Number > Time from the menu bar.
For cell D2, we’ll choose Format > Number > Duration, as it will display the length of time between the two specified times.
As you may have already guessed, we will find the time difference between 9:00:00 am and 5:32:49 pm. To do this, in cell D2, we will write the following formula and press Enter:
In cell D2, you will see the time difference.
If you want to extract hours from your result, enter
=HOUR(D2) in cell. To extract minutes and seconds, use
=SECOND(D2) in any cell, respectively.
This is how you add, subtract and find the time difference in your Google Sheets. Easy!
Do you want to count the days between two dates in Google Sheets? If so, it’s also easy to do.
RELATED: How to count the days between two dates in Google Sheets