# Google Sheets TIME Function Explained [2024 Update]

May 8, 2024

The TIME function in Google Sheets is used to create a time value from hours, minutes and seconds. The function returns a time value, which is a decimal number representing the time of day.

## TIME Function Syntax in Google Sheets

The syntax for the TIME function is:

=TIME(hour, minute, second)

Where

Hour, minute, and second are for the given hour, minute, and second to be merged to a time format. They can be either inserted inside the formula or you can set the reference to the cell containing the values.

Note:

The hour value should be between 0 and 23

the minute value should be between 0 and 59,

and the second value should be between 0 and 59.

## Google Sheets TIME Function Examples

### Using The TIME Function with Whole Numbers

The TIME function can use whole number values stored in separate cells. Simply specify the cell references within the function.

#### 1. Identify the cells with the hour, minute, and second values.

Example: A sheet containing the hour, minute, and second values are stored as follows:

The hour values are stored in Column A; the minute values are stored in Column B; and the second values are stored in Column C.

In our example the input values in row 2 we will use for TIME function are the following:

Hour: A2

Minute: B2

Second: C2

#### 2. Enter the formula =TIME(hour, minute, second) in a blank cell

In our example we will set up the formula for row 2 as:

=TIME(A2,B2,C2)

And insert it to the cell D2 under the time heading.

Alternatively, you can do the following:

1. Type =TIME( in cell D2

2. Click A2, B2, and C2 in order while hold-pressing the Ctrl button (Command on Mac)

3. Type

#### 3. Press Enter

You can drag the formula down the column with the blue square in the bottom right hand corner of the cell or apply Autofill suggestion (if it appears) to also convert the other rows using TIME function:

### Using The TIME Function with Decimals

Using the TIME function with decimals is similar to whole numbers except when decimals are present it removes the decimals and only converts the whole numbers to time:

#### 1. Identify the hour, minute, and second

In our example, the hour values are stored in Column A; the minute values are stored in Column B; and the second values are stored in Column C.

For Row 2 that means that the references to the input values for TIME function are the following:

Hour: A2

Minute: B2

Second: C2

#### 2. Enter the formula =TIME(hour, minute, second) in a blank cell

The formula becomes:

=TIME(A2,B2,C2)

And insert it to the cell in column D:

Alternatively, you can do the following:

1. Type =TIME( on cell D2

2. Click A2, B2, and C2 in order while hold-pressing Ctrl button

3. Type )

#### 3. Press Enter

You can can fill down the formula for the other rows or apply Autofill suggestion (if it appears) to also convert the other values using the TIME function:

### Using the TIME Formula when a Value is More than 24

When the hour value is more than 24, the function resets the count back to 0 and then converts it to its corresponding time. This means that if the hour value was 25, the TIME function would display 1:00 AM.

This is usually 12:00 AM for the 12-hour scheme.

To keep track of the number of days, you can add another column for the day count. The formula to calculate the number of days from the number of hours is:

=INT(hour/24)

The following steps will detail how to convert the values to time and also count the number of days:

#### 1. Identify the cells containing the hour, minute, and second values

In our Example:

The hour values are stored in Column A;
the minute values are stored in Column B;
and the second values are stored in Column C.

We set two columns D and E for the day and time, respectively.

For Row 2 that means that the cell references for the TIME function formula will be:

Hour: A2

Minute: B2

Second: C2

#### 2. Set up the formula for day =INT(hour/24)

For the first row in our data set, the formula becomes:

=INT(A2/24)

And we insert the formula into cell D2:

Alternatively, you can do the following:

1. Type =INT( in cell D2

2. Click A2

3. Type /24)

4. Press Enter

#### 3. Set up the Time formula =TIME(hour, minute, second)

We can now set up the time formula:

=TIME(A2,B2,C2)

And insert it to the cell E2:

Alternatively, you can do the following:

1. Type =TIME( on cell D2

2. Click A2, B2, and C2 in order while hold-pressing Ctrl button

3. Type )

If you want to apply the formulas to several rows, you can drag the formula down to the other rows or apply the Autofill suggestions if they appear.

### Using the TIME Function when Minutes or Seconds are Greater Than 60

If the minute and/or second value is more than 60, the function automatically adjusts the count of other values:

If the second value is more than 60, then the TIME function adds 1 to the minute value.

If the minute value is more than 60, then the TIME function adds 1 to the hour value.

#### 1. Identify the hour, minute, and second values

In our example we have three rows:

Row 2 has a minute value greater than 60;

Row 3 has a second value more than 60;

Row 4 has both minute and second values greater than 60.

The hour values are stored in Column A;
the minute values are stored in Column B;
and the second values are stored in Column C.

For Row 2 in our example the input values for TIME formula are:

Hour: A2

Minute: B2

Second: C2

#### 2. Create the formula =TIME(hour, minute, second)

The formula becomes:

=TIME(A2,B2,C2)

We will add it to cell D2.

Alternatively, you can do the following:

1. Type =TIME( in cell D2

2. Click A2, B2, and C2 in order while pressing and holding the Ctrl button (Command on a Mac)

3. Type )

#### 3. Press Enter

Note how the hour value was adjusted due to minute value exceeding 60:

You can drag down the formula or apply the Autofill suggestion (if it appears) for the other rows:

We hope this article has helped you and given you a better understanding of the Google Sheets TIME function. You might also like our articles on how to subtract dates in Google Sheets and how to use the Google Seets UNIQUE function.

To optimize your workflow, we recommend reading our guide on how to attach Google Sheets to an email.

Get Google Sheets productivity and automation tips delivered straight to your inbox