How to Use TIMEVALUE Function in Excel (4 Examples)

Excel’s TIMEVALUE function converts a text time into a decimal number between 0 (12:00:00 AM) and 0.999988426 (11:59:59 PM). The text time can be in any valid time format such as 12:00 AM, 4:30:35 PM, 15:30, 12-May-2020 13:00, or 7/19/2018 1:00 AM.

TIMEVALUE Function in Excel: Syntax

❑ Objective

The TIMEVALUE function converts a text time to an Excel serial number for a time. These numbers range from 0 (12:00:00 AM) to 0.999988426 (11:59:59 PM).

❑ Syntax

TIMEVALUE(time_text)

❑ Argument Explanation

❑ Output

A decimal number between 0 to 0.999988426.

❑ Version

First introduced in EXCEL 2000 and available in all subsequent versions.

Example 1 – Obtaining the Decimal Value of a Time

Suppose we have a dataset that has some times in text format, and we want to convert these into decimal values.

Steps:

• In cell D5, enter the following formula:

The function returns the decimal value of the time format text in cell B5.

• Press ENTER.

The decimal value of the time text is returned.

In a similar way, we can convert text representing times in any valid format to a decimal value.

Example 2 – Obtaining the Decimal Value of a Time from a Date with Time

Now let’s calculate the decimal value from a text representing both date and time.

Steps:

• In cell C5, enter the following formula:

The function will ignore the date part of the text and return a decimal value only for the time part.

• Press ENTER

The decimal value of the time is returned.

In a similar manner, we can convert time text which represents both date and time in any valid format to a decimal value.

Example 3 – Using TIMEVALUE Function in a Calculation

Suppose we have a dataset containing the entry time and leaving time of the employees of a company. If an employee works for 8 hours, it is considered a full day of work. Let’s use the time function to determine what portion of a full day’s work is done by different employees.

Steps:

• Enter the following formula in cell E5:

Here, the TIMEVALUE function converts the times of cells C5 and D5 to their decimal values. By subtracting the first value from the second, we get the difference between the entry time and leaving time. The TIMEVALUE function considers 24 hours to be a full day. The (24/8) portion converts the full days into 8 hours. So, if any employee works for 8 hours, the formula will return a value of 1.

• Press ENTER.

The fraction of the full day’s work done by the employee (Mark) is returned in cell E5.

• Drag the Fill Handle on cell E5 down to apply the same formula for all other employees.

Example 4 – Separating Date and Time

We can separate Date and time into two different columns. To separate the date part we will use the DATEVALUE function and the LEFT function, and for the time part we will use the TIMEVALUE function and the MID function. Suppose, we have some text containing date and time in column B, and want to separate the date part into column C and the time part into column D.

Steps:

To get the date part:

• Enter the following formula in cell C5:

The LEFT function will return the first 11 characters from the text in cell B5, then the DATEVALUE function converts it into a date,

• Press ENTER and select Date as the cell format.

The date part of the text B5 is shown in cell C5.

To get the time part:

• Enter the following formula in cell D5:

The MID function will return the 5 characters starting from the 13th position in the text in cell B5, then the TIMEVALUE function converts it into a time,

• Press ENTER and select Time as the cell format.

The time part of the text in cell B5 is inserted in cell C5.

In a similar manner, we can separate the date and the time from all valid text formats.

TIMEVALUE Function – Things to Remember 

• The TIMEVALUE function only converts text. If you provide any other formats, even the time format, the function will return a #VALUE! error.
• The text must be a valid time format with correct syntax. If you give the input text in improper time format, the function will return #VALUE! Error.

• However, we can convert time format into text format using the TEXT function and then applying the TIMEVALUE function.

• If two values are separated with a colon, for example 20:45, the string will be considered as hours and minutes, not minutes and seconds. To input only minutes and seconds, provide 00 as the hour input (00:20:45).

Download Practice Workbook

<< Go Back to Excel Functions | Learn Excel