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**

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

time_text |
Required | A text string representing a time |

**❑ 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:

`=TIMEVALUE(B5)`

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:

`=TIMEVALUE(B5)`

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**:

`=(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 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**:

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

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**:

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

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**