Time Function in Google Sheets

What is Time Function in Google Sheets?

The TIME function in Google Sheets converts time into a serial number based on the input hour, minute, and second. It creates a time value using the given hours, minutes, and seconds and returns the value, which is a decimal number representing the time of day.

As we know, Google Sheets has many time functions like TIME, HOUR, MINUTE, SECOND, etc., which help us calculate time and accurately manipulate and analyze time data. These calculations are useful, especially when managing, tracking, and calculating time for scheduling work hours, calculating the time taken to complete tasks, etc. In this simple example, we will create a time value representing 4 hours, 20 minutes, and 30 seconds. Enter the following formula:

=TIME(4, 20, 30). It will return 4:20:30 AM

The TIME function is used to output a time value based on the hour, minute, and second we supply as arguments.
The syntax of the TIME function is as follows:

=TIME(hour, minute, second)

hour: A number for the hour (0 to 23).

minute: A number for the minute (0 to 59).

second: A number for the second (0 to 59).

We can use the TIME function for adding or subtracting time values and in other time-based calculations.
The time values are displayed 12-hour format with AM and PM by default.

Syntax

TIME is not so easy to calculate. There are times when we could make errors in time calculation. However, the TIME function ensures accuracy in time-based calculations

The Time function in Google Sheets formula is as follows:

‍=TIME(hour, minute, second)

‍where

hour, minute, and second are for the given values of the hour, minute, and second that will be merged into a time format.

hour: number representing the hour between 0 to 23.
minute: a number representing the minute between 0 to 59.
second: minute: a number representing the second between 0 to 59.

You can either supply direct values or give cell references containing these values.

How To Use TIME Function in Google Sheets?

You can use the TIME function in calculations such as adding or subtracting time values. Before proceeding, let us look at how to enter TIME in Google Sheets.

Entering the TIME function in Google Sheets can be done in two ways.

Entering the TIME in Google Sheets manually
Through the Google menu bar

Entering the TIME in Google Sheets Manually.

Let us implement the time function to display it in a time format.

Step 1: A sheet containing the hour, minute, and second values is stored as follows. Let’s calculate the time in a proper format. Enter the formula =TIME( in a blank cell, C3 in this case.

Step 2: Now, we enter the cell references of the hour, minute, and second values and close the braces. The formula is as follows.

=TIME(B1,B2,B3)

Step 3: Press the “Enter” key. The TIME function uses the number values stored in the cells and gives us the time in a proper format.

Using the Google Menu Bar

Go to “Insert” → “Function” → “Date” → “TIME”.
Input the values for hour, minute, and second.
Press the “Enter” key.‍If you don’t fill in the argument, you must enter the “()”, parenthesizes.

Examples

Both dates and times in Google Sheets are represented as numbers. The date is a whole number, and the time is a decimal fraction of a day. With this knowledge, let us proceed with some interesting examples of using the TIME function in Google Sheets.

Example #1

Time zones can be very confusing when it comes to time management, especially when the teams in a business are located across different time zones. Thus, Google Sheets is very useful in this scenario as it provides a simple way to manage this using the TIME function. The TIME function alone doesn’t manage these time zones, but you can use it with other functions.

Let us try some of these conversions. You are located in Washington, D.C., and the local time is 10:00 AM when you wish to set up a meeting with someone in Dubai.

Dubai follows the Gulf Standard Time (GST), which is 9 hours ahead of Washington’s Eastern Standard Time.

Let us adjust the time zone accordingly.

Dubai Time: TIME(10, 0, 0) + TIME(9, 0, 0)

TIME(10, 0, 0)- signifies Washington time

TIME(9, 0, 0) – signifies Dubai time

Step 1: Enter the following formula in Google Sheets. Press Enter.

Dubai’s time is 7 PM. The time function gives you a time value that helps you understand the correct meeting time in Dubai.

You can format the time to be displayed in the required format by going to the following link.

Format -> Number -> Time.

If you prefer a customized format, you can select “Custom time” and specify the format you want.

Example #2 – Calculating Elapsed Time

We can calculate elapsed time using simple subtraction. Here, elapsed time means the difference between two time values. In the table below, we have some values. Using the TIME function, we find the time in a proper format and subtract it to get the elapsed time.

Step 1: We have the start and end times above. To find the time elapsed for each, let us use the following formula in cell E3.

=(TIME(C3,D3,0)-TIME(A3,B3,0))

The first time function converts the end time in terms of time, and the second function does the same for the start time; subtracting the two, we get the time elapsed or the time difference.

Step 2: Press Enter and drag the function for the other times as well.

Step 3: As observed, you get the result with the AM. To change the format to the time duration or difference, go to Format->Number and select Duration.

Step 4: Now, when you apply it to get the elapsed time for each of the times present.

Example #3 – Handling Time Zones with the TIME Function

Let us look at an interesting example of using the TIME function in a scenario of different time zones. With Google Sheets, you can easily convert time from one time zone to another using TIME as a basic formula. You can also use the NOW() function to find for time differences between the zones.

Step 1: To convert Pacific time to GMT, let us enter the original Pacific time in a cell.

Step 2: In C1, enter the formula as follows

=B1+TIME(8, 0, 0)

The TIME(8,0,0) specifies the time difference between Pacific Time and GMT. We use a positive value as GMT is 8 hours ahead.

Press Enter.

Step 3: Hence, the GMT for the Pacific time of 10:30 AM is 6:30 PM.

We can convert between time zones using the NOW() function and time differences. Here, to display the Central European Standard Time while your spreadsheet is set to Indian Standard Time (IST), use:

=NOW()+TIME(-4, 30, 0).

It is because the CEST is 4 hours 30 minutes behind IST. Press Enter.

Important Things to Note

Calculating elapsed time is very crucial for project management and scheduling tasks and can be easily done using simple subtraction with the TIME function.
The TIME function is used with other functions to adjust the time for time zones.
The input values to the TIME function should be numbers.
Subtracting and adding time can be done without errors. For instance, using TIME function in Google Sheets, a formula like =B2 – TIME(1, 30, 0) subtracts 1 hour and 30 minutes from the time in cell B2.

Frequently Asked Questions (FAQs)

What are some of the most used time functions in Google Sheets?

Besides the TIME function, we have many other functions like Google Sheets TIMEVALUE, which help perform calculations with time. Here are some other date and time functions in Google Sheets:

1. TIMEVALUE: This function returns the fraction of a 24-hour day that a particular time represents.
2. NOW: It returns the current date and time as a date value.
3. TODAY: It returns the current date without the time component.
4. HOUR: It extracts the hour from a time value.
5. MINUTE: This extracts the minute from the time. 6. SECOND: It gives the second component of a specific time.

How to add and subtract time in Google Sheets?

Time calculations require us to add or subtract time intervals. The TIME function in Google Sheets easily helps us obtain the same using arithmetic operators like + and -. It is important to ensure that accuracy is maintained while performing these calculations.

We use the following formulas to add hours, minutes, and seconds in Google Sheets.

Hour: =Start time + TIME(x hours, 0, 0)
Minute: =Start time + TIME(0, x mins, 0)
Second: =Start time + TIME(, 0, x secs)

Similarly, for subtraction of time, we use the minus sign.

How to display the output of the TIME function in a 12-hour format (AM/PM)?

Use the TEXT function with TIME in Google Sheets to display the result of the TIME function in 12-hour format (AM/PM) or in your required format.

For example, use =TEXT(TIME(16, 20, 0), “h:mm AM/PM”)

This will return the time as 4:30 PM.

Download Template

This article must help understand Time Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

TIME in Google Sheets Download

Time Function in Google Sheets