Don’t forget to share this post

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:

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.

How to Add or Subtract Time in Google Sheets Time Format
How to Add or Subtract Time in Google Sheets - Time Format

Duration Format

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

How to Add or Subtract Time in Google Sheets Duration Format
How to Add or Subtract Time in Google Sheets - Duration Format

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.

How to Add or Subtract Time in Google Sheets Duration Formula
How to Add or Subtract Time in Google Sheets - Duration Formula

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.
How to Add or Subtract Time in Google Sheets Select First Time
How to Add or Subtract Time in Google Sheets - Select First Time
  1. 2. Add the minus sign (-) and select the start time.
How to Add or Subtract Time in Google Sheets Subtract Second Time
How to Add or Subtract Time in Google Sheets - Subtract Second Time
  1. 3. Press ‘Enter’ to see the result. Double-click on the fill handle to copy the formula down.
How to Add or Subtract Time in Google Sheets Time Difference
How to Add or Subtract Time in Google Sheets - Time Difference
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

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.
How to Add or Subtract Time in Google Sheets Select Time
How to Add or Subtract Time in Google Sheets - Select 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.
How to Add or Subtract Time in Google Sheets Add Hours Minutes Seconds
How to Add or Subtract Time in Google Sheets - Add Hours, Minutes, & Seconds
  1. 3. Press ‘Enter’ to see the result. Double-click on the fill handle to copy the formula down.
How to Add or Subtract Time in Google Sheets New Time
How to Add or Subtract Time in Google Sheets - New Time
  1. 4. To subtract, repeat the steps above using the minus (-) instead of the plus (+) sign.
How to Add or Subtract Time in Google Sheets Subtract Duration from Time
How to Add or Subtract Time in Google Sheets - Subtract Duration from Time

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).

How to Add or Subtract Time in Google Sheets Insert Current Time Shortcut
How to Add or Subtract Time in Google Sheets - Insert Current Time Shortcut

Add Current Date & Time with NOW() Function

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

How to Add or Subtract Time in Google Sheets NOW Function
How to Add or Subtract Time in Google Sheets - NOW() Function
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

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.

How to Add or Subtract Time in Google Sheets Use SPLIT to Extract Time
How to Add or Subtract Time in Google Sheets - Use SPLIT to Extract Time

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

Extract Hour Component

How to Add or Subtract Time in Google Sheets Extract Hour
How to Add or Subtract Time in Google Sheets - Extract Hour

Extract Minute Component

How to Add or Subtract Time in Google Sheets Extract Minute
How to Add or Subtract Time in Google Sheets - Extract Minute

Extract Second Component

How to Add or Subtract Time in Google Sheets Extract Second
How to Add or Subtract Time in Google Sheets - Extract Second

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.

How to Add or Subtract Time in Google Sheets TIMEVALUE Function
How to Add or Subtract Time in Google Sheets - 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.

How to Add or Subtract Time in Google Sheets Times as Fractions
How to Add or Subtract Time in Google Sheets - Times as Fractions

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

  • Centralized Knowledge: Access your team’s collective wisdom with ease.
  • Collaborative Workspace: Foster engagement with flexible project tools.
  • Seamless Communication: Connect your entire organization effortlessly.
  • Preserve Ideas: Capture insights without losing them in chats or notifications.
  • Comprehensive Platform: Manage all content in one organized location.
  • Open Teamwork: Empower employees to contribute, share, and grow.
  • Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

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:

Maria Del Olmo
Originally published Mar 24 2023, Updated Mar 22 2023