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.

    Time
    Select the cell containing the time.
  2. From the “Format” menu, select the Number option. Then from the sub-menu, choose the Number format.

    Number Format
    Choose the Number format from the Format menu.
  3. You’ll notice a decimal number.

    Decimal Number
    Time value in the decimal format.

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.

Example
Example to show the steps of adding hours to time.

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.

    Hours Decimal Value
    Divide the hours by 24.
  2. In another cell, add the time and the decimal number calculated above. Then, press Enter.

    Add Hour to Time
    Add time and the decimal value of the hour.

Adding minutes to time

Example 1

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

Example
Example to show the steps of adding minutes to time.

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.

    Seconds Decimal Value
    Divide the hours by 1440.
  2. In another cell, add the time and the decimal number calculated above. Then, press Enter.

    Add Second to Time
    Add time and the decimal value of the minute.

Add Seconds to Time

Example 1

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

Example
Example to show the steps of adding seconds to the time.

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.

    Seconds Decimal Value
    Divide the hours by 86400.
  2. In another cell, add the time and the decimal number calculated above. Then, press Enter.

    Add Seconds to Time
    Add time and the decimal value of the seconds.

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.

    Hours Decimal Value
    Divide the hours by 24.
  2. Calculate the decimal value of the hour you want to add by dividing it by 1440.

    Minutes Decimal Value
    Divide the hours by 1440.
  3. Calculate the decimal value of the hour you want to add by dividing it by 86400.

    Seconds Decimal Value
    Divide the hours by 86400.
  4. Add the current time to all the 3 values calculated above. Then, press Enter.

    Add Time
    Add time and the decimal value of the hours, minutes, and seconds.

Was this article helpful?

Leave a Comment