How to Add Time in Google Sheets

When you perform calculations with times in Google Sheets, the results don’t always match your intentions. This guide shows you how Google Sheets handles time and how you can add time values together.

How is time represented in Google Sheets?

Time is represented in Google Sheets as a decimal number. To check the unit of time value, do the following:

1. Select the cell containing the time value.

   

2. From the “Format” menu, select the Number option. Then from the sub-menu, choose the Number format.

   

3. You’ll notice a decimal number.

   

The decimal number represents the parts of days, where 24 hours are represented with the value 1. Accordingly, the value 1/24 represents one hour. For example, 4.5 hours is represented as 4.5/24, which is 0.1875. In other words, 4.5 hours is 18.75% of a day.

Now that we know that 1 is represented as 24 hours, we can use the following for ease of calculations:

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

Trying to add time values together

Adding hours to time

Example 1 – using logic

When you try to add 5 (hours) to 3:00:00 AM, there is an inconsistency in the units. 5 is a plain number, whereas 3:00:00 is a fraction of a 24-hour day. Therefore, you will have to convert the plain number into a fraction of a 24-hour day before adding it to the time.

Therefore, you will have to add 5/24 (0.208) to the original value, which is 3/24 (0.125), which is 0.333. 0.333 multiplied by 24 will give us 7.992, or rounded up, 8. Therefore, the time will be 8:00:00 AM.

Example 2 – using an equation

Suppose you want to add 1.5 hours to 1:30:00 PM.

To add hours to the time, do the following:

1. In a cell, enter “=” to start the calculation. Then, type the hours you want to add divided by 24. Then, press Enter. You will notice a number in decimal format.

   

2. In another cell, add the time and the decimal number calculated above. Then, press Enter.

   

Adding minutes to time

Example 1

Let’s say you want to add 25 minutes to 1:30:00 PM.

To add minutes to the time, do the following:

1. In a cell, enter “=” to start the calculation. Then, type the minute you want to add divided by 1440. Then, press Enter. You will notice a number in decimal format.

   

2. In another cell, add the time and the decimal number calculated above. Then, press Enter.

   

Add Seconds to Time

Example 1

Say you want to add 16 seconds to 1:30:00 PM.

Follow the below steps to add seconds to the time, do the following:

1. In a cell, enter “=” to start the calculation. Then, type the minute you want to add divided by 86400. Press Enter. You will notice a number in decimal format.

   

2. In another cell, add the time and the decimal number calculated above. Then, press Enter.

   

Add Hours, Minutes, and Seconds Together to Time

To add Hours, Minutes, and Seconds together to a given time, follow the below steps:

1. Calculate the decimal value of the hour you want to add by dividing it by 24.

   

2. Calculate the decimal value of the hour you want to add by dividing it by 1440.

   

3. Calculate the decimal value of the hour you want to add by dividing it by 86400.

   

4. Add the current time to all the 3 values calculated above. Then, press Enter.