TIME Function in Excel: Explained

In this article, you will learn how to use the TIME function in Excel.

‍

What is the TIME function in Excel?

In Excel, the TIME function is a function that allows you to create a time value from given values. The function takes three arguments, hours, minutes, and seconds and returns the time based on those inputs. And so the TIME function is not volatile, meaning it will not display or update to the current time.

‍

When is the TIME function in Excel beneficial?

The TIME function in Excel is beneficial when you need to create, manipulate, or analyze time values in your spreadsheet. It allows you to combine individual hour, minute, and second values into a single time value, which can then be used for various calculations or data analysis. Some examples include:

1. Scheduling: The TIME function can be used to create and manage schedules, such as employee work hours, project timelines, or event planning.
2. Data analysis: The TIME function can be useful for analyzing time-based data, such as call durations, website visit durations, or process times. By combining it with other functions and tools, you can gain insights and make data-driven decisions.

‍

How to use the TIME function in Excel

As stated above, the TIME function requires three arguments, the syntax is as follows:

=TIME(hour,minute,second)

Hour: This is a required argument representing the hour component of the time value. It can be an integer between 0 and 32767.

Minute: This is a required argument representing the minute component of the time value. It can be an integer between 0 and 32767

Second: This is a required argument representing the second component of the time value. It can be an integer between 0 and 32767

‍

Note: You may input time values that go beyond the standard range of time, meaning you can input hours larger than 23, minutes larger than 59, and seconds larger than 59. However, the hour, minute, or second value cannot be larger than 32767, this will result in the TIME function returning the #NUM! Error. Excel will simply divide by the standard range with remainders treated as hour, minute, or second values. An example can be seen below.

‍

Note of Caution: Keep in mind that the hour inputs are in 24-hour time so if you are looking to display your time in terms of AM/PM, be careful with what you input. For example, if you want to display 7:30 PM, write =TIME(19,30,0) and not =TIME(7,30,0).

‍

Below is a screenshot of examples of using the TIME function and possible errors.

‍

Formatting of TIME in Excel

The time format that the TIME function will return is dependent on your system's time settings, however, this is customizable to your choosing. You can do this by right-clicking the cell in which the time is displayed and clicking “Format Cells”. For instance, Excel may return a serial number representative of the fraction of the day such as 0.5 but you would want to display it as 12:00 pm. 

‍

What is the difference between the TIME and NOW functions in Excel?

The TIME and NOW functions in Excel are used for different purposes related to time:

• The TIME function is used to construct a time value from individual components of hours, minutes, and seconds. It does not return the current time or date. The values provided to TIME are not volatile and will not change unless manually edited.
• The NOW function, on the other hand, returns the current date and time. This function is volatile and updates every time the Excel sheet is recalculated.

‍

What is the difference between the TIME and TIMEVALUE functions in Excel?

While both the TIME and TIMEVALUE functions in Excel deal with time values, they serve different purposes and require different inputs:

• The TIME function is used to create a time value based on individual components of hours, minutes, and seconds. It requires three arguments: hour, minute, and second. 
• The TIMEVALUE function converts a time stored as text to a serial number that Excel recognizes as a time. It requires a single argument: a text string that represents a time. For example, if you have a text string "13:30:00" and you want Excel to recognize it as a time, you could use =TIMEVALUE("13:30:00"). This function is especially useful when importing data from other sources that may store times as text.

‍

Go to the page LiveFlow‘s How to Guides to find more information about Excel and Google Sheets formulas and tips that were not covered here.

‍