Google Sheets is a flexible and easy-to-use spreadsheet program where you can do anything from basic arithmetic operations to complex data analysis. Best of all, you can work with different types of data easily, using the same symbols and operators. For example, you can calculate the difference between two clock times, add hours, days, and seconds to existing times, and get the time value of a specific time as a fraction of a day.

In this guide, you will learn different ways of adding and subtracting time in Google Sheets. First, you will review the difference between the time and duration formats, which allow you to work with time units in different ways. Second, you will learn how to find the difference between two specific times. Third, you will learn how to add and subtract hours, minutes, and seconds from a given time. Finally, you will learn how to insert the current time and how to extract the time from timestamps containing the date and time.

To learn how to add and subtract dates, check out our guides on:

• DATEDIF in Google Sheets: Formula & Examples
• How to Add Numbers in Google Sheets
• How to Subtract in Google Sheets Formula & Examples

Time & Duration Formats in Google Sheets

When working with time in Google Sheets, it’s important to keep an eye on cell format to avoid confusing or unexpected results. Depending on whether you want to represent a clock time or an amount of time, you should use the time or duration format, respectively.

Time Format

Google Sheets features various time formats so that you can choose which units you want to include: hours, minutes, and seconds. To set the time format, select the relevant cells and go to Format > Number.

Duration Format

To set the duration format, select the relevant cells and go to Format > Number > Duration.

When working with the duration format, Google Sheets understands integers as days. In other words, One is equal to twenty-four hours: 1 = 24 hours. To represent shorter periods of time, you can use the corresponding fractions.

• 1 hour = 1/24
• 1 minute = 1/(24*60)
• 1 second = 1/(24*60*60)

Below, you have examples of how to add different amounts of time to cells with the duration format.

However, you can also use the duration syntax to specify the amount of time: hh:mm:ss. For example, if you want to add one hour, one minute, and one second, use “01:01:01”.

How to Calculate the Difference Between Times in Google Sheets?

To get the difference between two times in Google Sheets, you can use the minus (-) sign or the MINUS function. First, ensure that the two times are in the appropriate time format. Second, apply the duration format to the cells where you want to subtract the times.

1. 1. In an empty cell, type the equal sign and select the end time.
   

1. 2. Add the minus sign (-) and select the start time.
   

1. 3. Press ‘Enter’ to see the result. Double-click on the fill handle to copy the formula down.
   

How to Add & Subtract Hours, Minutes, and Seconds in Google Sheets?

If you want to add or subtract a specific number of hours, minutes, or seconds to or from an existing time, you can combine the time and duration formats. The amount of time you want to add can be expressed using duration syntax or the corresponding fraction of a day, as shown in the first section. For simplicity, the examples below all use the duration syntax.

Follow the steps below to add a specific number of hours, minutes, and seconds to an existing time. Remember to include the zeroes for units you’re not using.

1. 1. In an empty cell, type the equal sign and select the cell with the time.
   

1. 2. Add the plus (+) sign and select the cell with the amount of time you want to add. In this case, I have combined the units in different ways.

1. 3. Press ‘Enter’ to see the result. Double-click on the fill handle to copy the formula down.

1. 4. To subtract, repeat the steps above using the minus (-) instead of the plus (+) sign.

Add Current Time in Google Sheets

Adding the current time is easy in Google Sheets: you can use keyboard shortcuts to insert static timestamps or use the NOW() function to insert a dynamic timestamp that also includes the date.

Add Current Time with Shortcut

To insert a static timestamp with the current time, use the following shortcut: Ctrl + Shift + ; (Windows) or Cmd + Shift + ; (Mac).

Add Current Date & Time with NOW() Function

To insert a dynamic timestamp with the current date and time, use the NOW() function.

Extract Time from Date & Time Format

If you have a timestamp that includes the date and time, you can easily extract the time using the time functions: HOUR, MINUTE, & SECOND. In fact, you can extract units of time from any timestamp.

Extract Full Time

To extract the full time, you can use the SPLIT function.

To extract the individual time components, use the HOUR, MINUTE, or SECOND function and select the cell containing the time.

Extract Hour Component

Extract Minute Component

Extract Second Component

Use TIMEVALUE to Convert Time to Fraction

You can use the TIMEVALUE function to express a specific time as a fraction of a day. Select the cell where you want the fraction and type the TIMEVALUE function.

After selecting the cell containing the time, close the parenthesis and press ‘Enter’. Double-click or grab the fill handle to copy it down to the last row.

Conclusion

Working with time units in Google Sheets is easy. You know the difference between the duration and time formats and when to use each. You also know how to calculate the difference between two times and how to add or subtract hours, minutes, and seconds from an existing time.

You also know how to insert the current time or the date and time using a keyboard shortcut and how to use the NOW() function to get a dynamic timestamp with the date and time. Finally, you know how to extract the time or individual time components from a cell with the date and time format.

To learn how to add and subtract dates, check out these guides:

• DATEDIF in Google Sheets: Formula & Examples
• How to Add Numbers in Google Sheets
• How to Subtract in Google Sheets Formula & Examples