Here's How to Convert Time into Decimal Value Using Excel's TIMEVALUE Function

TIMEVALUE Function: A Brief

The TIMEVALUE function in Excel is used to convert a time string into a decimal number that Excel recognizes as a time value. This decimal can then be formatted as a time or used in calculations. This is especially useful when your time is in text format and you want to convert it to an actual time value that Excel understands.

Note: TIMEVALUE function will return #VALUE! error, when there is an issue with your inputs. You can fix this by providing a proper time string without any hidden characters.

TIMEVALUE Function: A Syntax

=TIMEVALUE(time_text)

• time_text: A time written as text, such as “6:30 PM” or “09:15”.

The function returns a decimal number between 0 and 1:

Basic Examples:

In the below example, I’ve covered most of the common ways to use the TIMEVALUE function are shown using different types of input values.

Basic example of using TIMEVALUE Function with common scenarios.

From the above examples, you can understand the following:

• In the first case, the function returns a decimal value because the input is a valid 12-hour time with PM.
• In the second case, the input has a leading zero, but the function ignores it and returns the correct decimal value, as it is still a valid 12-hour PM time.
• In the third case, the input is in 24-hour format, so the function returns the appropriate decimal value.
• In the fourth case, the time is entered in 12-hour format without AM/PM, so Excel assumes AM by default and returns the corresponding value.
• In the fifth case, the input includes a date and time, but the function ignores the date and returns the decimal value for the time only.
• In the sixth case, even though the input has a very old date and time, the function extracts and returns only the time part as a decimal.
• In the seventh case, the input time goes beyond 24 hours, so the function resets and returns 0, which is 12:00 AM in Excel.
• In the eighth case, the input includes hour, minutes, and seconds, so the function returns a precise decimal value.
• In the ninth case, only the minutes are entered (e.g., 0:25), and Excel interprets this as 25 minutes past midnight, returning the equivalent decimal.
• In the tenth case, the input includes only the seconds (e.g., 0:0:20), and Excel treats it as 20 seconds, returning the corresponding decimal.
• In the eleventh case, the input is not a valid time, so the TIMEVALUE function returns a #VALUE! error.

Note: In Excel, you can convert these decimal values to actual time format by entering them in a cell and applying Time formatting.

Next, select “Time” from the menu on the left. Then, under the “Type” section, choose how you want the time to be formatted and shown in the cells.

After selecting your preferred format, click the OK button to apply the changes.

The equivalent values of time and its decimal value.

Using TIMEVALUE Function with other Nested Functions:

The example below explains how you can combine the TIMEVALUE function with other nested functions for common use cases.

Using TIMEVALUE function with other Nested functions

For the given input time, 5:15 PM as the input value.

That’s it. This tutorial is originally published on How to Use Excel TIMEVALUE Function?