Many spreadsheet operations require time calculations, whether you’re tracking project durations, figuring out work shifts, or analyzing data across time zones. Google Sheets, a powerful cloud-based spreadsheet tool, offers powerful capabilities for adding and subtracting time quickly.
This article will demonstrate how to make precise time calculations in Google Sheets by explaining the methods of adding and subtracting Time.
What are the Time Formats used in Google Sheets?
Time formats that are followed in Google Sheets:
- 24-Hour Clock Format: This time format is the default time format in Google Sheets. In this format, hours range from 0 to 23, and minutes range from 0 to 59. For example, 9:30 AM is represented as 9:30, while 2:45 PM is represented as 14:45.
- AM/PM Format: If you prefer to use the AM/PM time format, you can customize the cell’s format in Google Sheets and to do this, select the range of cells, right-click, and choose “Format cells.” Under the “Number” tab, select “Time” and then choose the desired time format, such as “2:30 PM” or “02:30 PM.”
How to Add Time in Google Sheets?
To add time in Google Sheets, consider a Google Sheet of “Time”:
To add all the times, we can use the SUM() function of Google Sheets. To learn more about the SUM() function you can visit the blog of the sum() function with the if condition.
For this article, we will use the SUM() function of Google to add all the times:
When the “ENTER” key is pressed, the result of the addition of the time will be displayed:
The time is added and results are displayed in the 24-hour time clock.
How to Subtract Time in Google Sheets?
To subtract time in Google Sheets, we can again use the SUM() function by using the negative (-) sign with one of the values. For example, to subtract “Time D” from “Time C”, we will use:
Then press the “ENTER” key to display the results:
The time has been subtracted which is “Two hours, forty minutes, and thirty seconds” and displayed on the selected cell.
What is a Method to Extract Time Units in Google Sheets?
To make the calculations convenient, we can extract different time units from the time by using the built-in formulas of Google Sheets. Some of the built-in functions related to time are displayed:
- SECOND(): This function in Google Sheets is used to extract seconds from provided time.
- MINUTE(): This function in Google Sheets is used to extract minutes from provided time.
- HOUR(): This function in Google Sheets is used to extract hours from provided time.
To add and subtract time in Google Sheets, we can use the “SUM()” function and to subtract the time, we can use the “Negative(-)” sign with one of the values of the time. This blog explained both methods of adding as well as subtracting time in Google Sheets. To make the calculations more convenient, the extraction of the time units from the provided time is also explained in this blog.