Excel **TIMEVALUE **function (a **date & time **function) converts a text time in a decimal number between **0** (12:00:00 AM)** to 0.999988426 **(11:59:59 PM). The text time can be in any format which represents a time for example 12:00 AM, 4:30:35 PM, 15:30, 12-may-2020 13:00, 7/19/2018 1:00 AM.

The image above gives an overview of the **TIMEVALUE **function. You will get to know more about the function throughout the article.

## TIMEVALUE Function in Excel: Syntax

**❑ Objective**

Excel **TIMEVALUE** function converts a text time to an Excel serial number for a time, a number from **0 (12:00:00 AM)** to **0.999988426 (11:59:59 PM)**. Excel formats the number with a time format after entering the formula.

**❑ Syntax**

`TIMEVALUE(time_text)`

**❑ Argument Explanation**

Argument | Required/Optional | Explanation |
---|---|---|

time_text |
Required | A text string which represents a time |

**❑ Output**

The **TIMEVALUE** function returns a decimal number between **0** to **0.999988426** based on the input time which is represented by a text string.

**❑ Version**

This function was first introduced in **EXCEL 2000**. This function has been available in all versions of Excel since 2000.

Now we will see some examples of using the **TIMEVALUE** function which will help you to understand the function more clearly.

## 1. Using the TIMEVALUE Function in Excel to Obtain the Decimal Value of a Time

We can use the **TIMEVALUE **function to obtain the decimal value of a time text. Suppose we have a dataset that has some texts specified with only time. To get the decimal value,

➤ Type the following formula,

`=TIMEVALUE(B5)`

Here, the function will give the decimal value of the time text of cell **B5**.

➤ Press **ENTER**.

As a result, you will get the decimal value of the time text of cell **B5**.

In a similar way, you can convert time text which represents the time in any other format to a decimal value.

## 2. Getting the Decimal Value of a Time from a Date with Time with TIMEVALUE Function

Sometimes we have a text entry which has both date and time. Now, we will learn how to get the decimal value from a text representing both date and time using the **TIMEVALUE **function.

➤ Type the following formula in an empty cell (**C5**),

`=TIMEVALUE(B5)`

The function will ignore the date part of the text of the cell **B5** and give the decimal value only for the time part of that text.

Now,

➤ Press **ENTER **

As a result, you will get the decimal value in cell **C5** of the text which is representing both the date and time.

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

## 3. Using Excel TIMEVALUE Function in a Calculation

In this example, we will learn how we can use the **TIMEVALUE **function in a calculation of a real life scenario. Suppose, we have a dataset where entry time and leave time of the employees of a company are given. If an employee works for 8 hours, it is considered a full day of work. Now we will use the time function to determine what portion of a full day’s work is done by different employees.

➤Type the following formula in cell **E5**,

`=(TIMEVALUE(D5)-TIMEVALUE(C5))*(24/8)`

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

Now,

➤ Press **ENTER**.

As a result, you will get the fraction of the full day’s work done by the employee (*Mark***)** in cell **E5**.

At last,

➤ Drag the cell **E5 **to apply the same formula for all other employees.

As a result, you will get what portion of a full day’s work is done by all of the employees.

## 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**, now, we will separate the date part in column **C** and the time part in column **D**.

To get the date part,

➤ Type the following formula in **C5**,

`=DATEVALUE(LEFT(B5,11))`

The **LEFT **function will return the first **11 **characters from the text **B5** and the **DATEVALUE **function converts it into a date,

After that,

➤ Press **ENTER **and select **Date **as the cell format.

As a result, you will see that the date part of the text **B5** has been shown in cell **C5**

Now to get the time part,

➤ Type the following formula in **D5**,

`=TIMEVALUE(MID(B5,13,5))`

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

After that,

➤ Press **ENTER **and select **Time **as the cell format.

As a result, you will see that the time part of the text **B5** has been inserted in cell **C5**

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

## 💡 Excel TIMEVALUE Function: Things to Remember

📌 The **TIMEVALUE **function only converts text. If you give any other formats even the time format in the input the function will show **#VALUE! **error. The text must be a time represented properly. If you give the input text in improper time format, the function will return **#VALUE! **Error.

Though you can convert time format into text format using** the TEXT function** and then apply the **TIMEVALUE **function.

📌 If you give two values separating with a colon for example 20:45, it will be considered as the hours and minutes, not minutes and seconds. If you want to input only minutes and seconds, you must give 00 as the hour input (00:20:45).

**Download Practice Workbook**

## Conclusion

I hope now you know what the **TIMEVALUE **function is and how to use it in Excel. If you have any confusion please feel free to leave a comment.

**<< Go Back to Excel Functions ****|**** Learn Excel**