- Time & Duration Formats in Google Sheets
- How to Calculate the Difference Between Times in Google Sheets?
- How to Add & Subtract Hours, Minutes, and Seconds in Google Sheets?
- Add Current Time in Google Sheets
- Extract Time from Date & Time Format
- Use TIMEVALUE to Convert Time to Fraction
- Want to Boost Your Team's Productivity and Efficiency?
- Conclusion
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.
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. In an empty cell, type the equal sign and select the end time.
- 2. Add the minus sign (-) and select the start time.
- 3. Press ‘Enter’ to see the result. Double-click on the fill handle to copy the formula down.
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.
READ MOREHow 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. In an empty cell, type the equal sign and select the cell with the time.
- 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.
- 3. Press ‘Enter’ to see the result. Double-click on the fill handle to copy the formula down.
- 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.
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 MOREExtract 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.
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: