TIMEVALUE Excel

The TIMEVALUE Excel function is an inbuilt Date & Time function that accepts a time value specified as a text string and returns its decimal number equivalent. So, the function helps represent the time values from 12 AM to 11:59:59 PM with decimal numbers from 0 to 0.9988426.

Users can use the TIMEVALUE Excel function to convert the text-formatted time values into decimal numbers required in time calculations in financial statements.

For example, the following dataset contains a list of students and their project submission time data in text format in column B.

The requirement is to display the decimal equivalent of the time values in column C.

While we can use TIMEVALUE Excel VBA code to achieve the required output in the target cells, inserting the TIMEVALUE() directly in each target cell is more straightforward.

In the above example, the Excel TIMEVALUE function in each target cell accepts the cell reference to the corresponding text-formatted time value in column B. It then returns the specified time value’s decimal number equivalent.

However, if the given time value is not in the text format, then we will face the issue of the function TIMEVALUE Excel not working.

• The TIMEVALUE Excel function helps convert a text-formatted time value into its decimal number equivalent.
• Users can use the TIMEVALUE function to obtain the required time value as a decimal number, which is useful for performing time-based calculations in Excel.
• The TIMEVALUE function accepts one mandatory argument, time_text. And while we can insert the function in a cell directly or from the Formulas tab, VBA coding is another practical method.
• We can use the TIMEVALUE function as a standalone function or with other Excel functions, such as DATEVALUE, to yield practical results.

The TIMEVALUE Excel function formula is as follows:

Where,

• time_text: It is a mandatory argument, a text string representing a time value in an Excel-recognized time format. While we can supply the argument as a cell reference to a time value in the text format, the argument can be a valid time value within double quotes.

Please note that the time_text argument value must be a valid text value. Otherwise, we will see the function TIMEVALUE Excel not working and leading to the #VALUE! error.

Furthermore, the function ignores the date part in the time_text argument value.

While we can utilize the TIMEVALUE Excel VBA coding method to apply the TIMEVALUE() in a target cell, we shall see the simpler methods.

We can use the TIMEVALUE Excel function in two ways, namely,

1. Access from the Excel ribbon.
2. Enter into the worksheet manually.

Method #1 – Access From The Excel Ribbon

Choose an empty cell for output - select the Formulas tab - click the Date & Time option down arrow - choose the TIMEVALUE function, as depicted below.

The Function Arguments window opens. Enter the argument in the Time_text field - click OK, as depicted below.

Method #2 – Enter Into The Worksheet Manually

1. Choose an empty cell for the output.
2. Type =TIMEVALUE( in the cell.
3. Supply the argument as a cell value in double quotes or a cell reference and close the brackets.
4. Press Enter to get the required time value as a decimal number.

#Basic Example

The following dataset shows invoice data with the product delivery schedules in text format.

The aim is to display the product delivery schedule data in decimals in column D. Then, here is how we can use the TIMEVALUE Excel function in each target cell to achieve the required output.

Step 1: Select cell D2 and then the Formulas tab - Date & Time function group down arrow - TIMEVALUE function.

Step 2: The Function Arguments window will open, where we will enter the cell reference to the corresponding product delivery schedule value in the Time_text field value.

Clicking OK in the Function Arguments window will display the required decimal equivalent of the specified product delivery schedule value in the target cell.

Step 3: Update the formula in the remaining target cell using the Excel fill handle, provided the argument value is the cell reference to the corresponding product delivery schedule value.

The TIMEVALUE() accepts the date and time value. However, it ignores the date part and returns the specified time value’s decimal number equivalent.

While the above steps show how to use the TIMEVALUE(), determining the reverse TIMEVALUE Excel value is possible using the Excel TEXT function. We must provide the TIMEVALUE() output as the first argument and a valid time format in double quotes as the second argument to the TEXT(). The output will be the time value in the text format based on which we got the TIMEVALUE() return value.

The following examples explain the practical ways to use the TIMEVALUE Excel function.

Example #1

The following dataset lists employees and their swipe in/out times as text strings.

The requirement is to determine each employee’s work duration based on their swipe in and out times and display the output in column E. Assume 9 working hours count as a work duration of 1 day.

Then, here is how we can use the TIMEVALUE Excel function-based formula in each target cell to achieve the required output.

Step 1: Choose cell E2, enter the TIMEVALUE()-based formula and press Enter.

=(TIMEVALUE(D2)-TIMEVALUE(C2))*(24/9)

Step 2: Using the fill handle, update the formula in the remaining target cells.

Let us check the cell E6 formula to understand the logic.

First, the two TIMEVALUE() instances return the decimal number equivalents of the swipe in and out time text strings, 0.736111 and 0.298611. Next, the formula finds the difference between the two values, 0.4375. After that, the term 24/9 divides 24 hours into nine hours-work durations, which is 2.6666. Finally, the formula returns the product of the two numbers 0.4375 and 2.6666, 1.166666667, as the specific employee’s work duration in days.

Example #2

We shall see how to combine DATEVALUE and TIMEVALUE Excel functions to yield practical outcomes.

The following table shows the dates and times of different team meetings at a firm as text strings.

The requirement is to display each team meeting schedule in the date-time format in column D, based on the corresponding date and time text strings.

Then, we can combine DATEVALUE and TIMEVALUE Excel functions and use them in the target cells to achieve the required output.

Step 1: Choose the target cell range D2:D6 and then Home - Number Format drop-down button - More Number Formats option.

The Number tab in the Excel Format Cells window opens, where we must select the Date category and the required date-time format in the Type section.

Next, click OK in the Format Cells window to exit it.

Step 2: Choose cell D2, enter the Excel DATEVALUE function and TIMEVALUE Excel function-based formula, and press Enter.

=DATEVALUE(B2)+TIMEVALUE(C2)

Step 3: Using the fill handle, update the formula in the remaining target cells.

We shall check the cell D6 formula to understand its logic.

First, the DATEVALUE() returns the specified date value’s serial number equivalent, 45177. Next, the TIMEVALUE() returns the specified time value’s decimal number equivalent, 0.5833.

Finally, the formula adds the two values. But since we set cell D6’s data format as date-time, we see the formula output in the desired date-time format.

Example #3

We shall see how to ensure the TIMEVALUE Excel function does not return the #VALUE! error when the input time value is not a text string.

The following dataset lists websites and the log-in times into these websites by a student.

While the log-in time values are not text strings, the aim is to use them to get the decimal number equivalent of the time values and show the output in column C.

Then, if we use the TIMEVALUE() with the cell reference to the corresponding log-in time value as the argument value in each target cell, the output is the #VALUE! error.

The reason for the function to return an error value is that the supplied argument value is not a text string.

Thus, here is how to achieve the correct output.

Step 1: Choose cell C2, enter the following formula, and press Enter.

=TIMEVALUE(TEXT(B2,"HH:MM:SS"))

Step 2: Using the fill handle, update the formula in the remaining target cells.

Let us see the cell C6 formula to understand how it works.

First, the TEXT() accepts the cell B6 time value and returns it as a text string in the specified time format. Next, the TIMEVALUE() accepts the TEXT() output and returns the specified text-formatted time value’s decimal number equivalent.

Furthermore, we can supply the TIMEVALUE() output and a valid time format as the first and second arguments to the TEXT() to achieve the reverse TIMEVALUE Excel value.

• Ensure the argument value supplied to the TIMEVALUE Excel function is a text string. Otherwise, the function will return the #VALUE! error.
• When supplying the TIMEVALUE function argument as a value, ensure the value is in double quotes.
• The TIMEVALUE function will ignore the date part if provided along with the time value as the function argument value.

This article must be helpful to understand the TIMEVALUE Excel, with its formula and examples. You can download the template here to use it instantly.

This article is a guide to What Is TIMEVALUE Excel. Here we explain the TIMEVALUE Excel formula and how to use it with examples and points to remember. You can learn more about Excel functions from the following articles: –

• IF AND in Excel
• ISTEXT Excel
• Crash Course In Excel Pivot Table