How to Add Time in Google Sheets (The Right Way!)

Adding time in google sheets

‍

In order to add time in google sheets, you add the duration to the initial time.

‍

Google Sheets follows the 24-hour format by default. 

‍

You should match the format of what you want to add. If you simply add a number without specifying whether it refers to an hour, minute, or second, Google Sheets will interpret it as days:

If you set the date format to HH:MM or HH:MM:SS, it will hide the number of days added, as seen in Row 2 and 3. In Row 4 and 5, the added days show up because the datetime format was modified to display the days added as hours instead of rounding off the hours in excess of 24 (which is the default).

‍

If you want to specify the numbers as time, use the HH:MM and HH:MM:SS formats, whichever is applicable.

‍

Once you set the format correctly, you can use either the addition sign:

=final - initial

‍

Or SUM:

=SUM(final, initial)

HH:MM format

In Row 2, only minutes are added. To do so, simply set HH to zero, setting the format to 00:MM.

‍

In Row 4, the total number of hours is 25. As this is more than 24 hours, the sum thus resets back. 

‍

In Row 6, the value with HH:MM format in the first column was added with the value in the second column using the HH:MM:SS format. As the first value is in HH:MM format, the sum is in HH:MM format.

HH:MM:SS

Subtract Time to get Duration

‍

If you are given the initial and final, you can get the duration between them:

=final - initial

‍

Or you can use SUBTRACT:

=SUBTRACT(final, initial)

Like in the formulas for how to add time in Google Sheets, the format of the solution follows the format of the final time. For example, in Row 5, even though the initial is specified in HH:MM:SS, the duration is formatted as HH:MM because the final is formatted as HH:MM. 

‍

If the final time is “less than” that of the initial time (in terms of the numbers), Google Sheets will interpret the final as that of the next day. For example, in Row 4, the initial is set at 8:00 while the final is set at 7:30. Google Sheets counts the time from 8:00 this day to 7:30 the next day, thus giving the answer of 23:30.

‍

Functions to Calculate Time in Google Sheets

It is common for the time values to be stored in formats different from that used by Google Sheets. Here are the functions you can use to add time in google sheets:

TEXT function

The TEXT function converts number values to text by following a certain time format. You can use it if you do not want to modify the time format of the cell.

Syntax

=TEXT(number, format)

‍

Where

‍

number

The number to convert to text

‍

format

The format to apply to the number. There are several time formats you can use in Google Sheets. They can be combination of the following:

‍

HH - hours 24 hr format

hh - hours 12 hr format

mm - minutes

ss - seconds

ss.000 - seconds with milliseconds

AM/PM - add AM and PM to the 12 hr format

Example

HH:mm - to hour-minute format (24 hrs)

HH:mm:ss - to hour-minute-second format (24 hrs)

hh:mm:ss AM/PM - hour:minute:second format with AM/PM for 12-hr format

TIME Function

‍

The TIME function is used to combine separate values to a single value stored in a single cell. The syntax of the function is as follows:

=TIME(hour, minute, second)

‍

The function will automatically round off any input that exceeds its bounds (the maximum value for hour is 23 while for minute and second is 59).

HOUR Function

The HOURS function is used to extract the number of hours from a given time:

=HOURS(time)

‍

It automatically rounds off when the input hours exceeds 23.

MINUTE Function

The MINUTE function is used to extract the number of minutes from a given time:

=MINUTE(time)

‍

It automatically rounds off when the input exceeds 59.

SECOND Function

The SECOND function is used to extract the number of seconds from a given time:

=SECOND(time)

‍

It automatically rounds off when the input exceeds 59.

Add or subtract hours in Google Sheets

Use the following formula to add only hours:

=initial_time + hours/24

‍

While for subtraction you can use the following formula:

=initial_time - hours/24

The advantage of this method is that you can set the number of hours to be greater than 23 and that you can add hours with decimal portions as well (the decimals are processed by Google Sheets as minutes).

Add or subtract minutes in Google Sheets

Use the following formula to add only minutes:

=initial_time + minute/(24+60)

‍

While for subtraction you can use the following formula:

=initial_time - minute/(24+60)

The advantage of this method is that you can set the number of minutes to be greater than 60 and that you can add minutes with decimal portions as well (the decimals are processed by Google Sheets as seconds).

Add or subtract seconds in Google Sheets

Use the following formula to add only seconds:

=initial_time + second/(24+60+60)

‍

While for subtraction you can use the following formula:

=initial_time - second/(24+60+60)

The advantage of this method is that you can set the number to be greater than 60 and that you can add decimal portions as well. For the decimal portions, Google Sheets will round it off. Google Sheets can display milliseconds, but you have to customize the time format: