How to Use TIMEVALUE Function in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

EXCEL TIMEVALUE FUNCTION

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


📂 Download Practice Workbook


Introduction to The TIMEVALUE Function

❑ 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)

EXCEL TIMEVALUE FUNCTION

❑ 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.


4 Examples of Using The TIMEVALUE Function in Excel

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

1. TIMEVALUE Function to Obtain The Decimal Value of a Time Text

We can use the TIMEVALUE function to obtain the decimal value of a time text. Suppose we have a dataset which 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.

DATA

➤ Press ENTER.

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

ALL DATA

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

FORMULA TEXT

Read More: How to Use TIME Function in Excel (8 Suitable Examples)


2. Get The Decimal Value of a Time from a Date with Time

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.

TIMEVALUE

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.

EXCEL TIMEVALUE FUNCTION

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

Read More: How to Remove Time from Date in Excel (6 Approaches)


Similar Readings


3. Using 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.

CALCULATION

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.

VALUE

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.

EXCEL TIMEVALUE FUNCTION

Read More: How to Auto-Update Current Time in Excel (With Formula and VBA)


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.

DATASET

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

DATEVALUE

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

TIMEVALUE

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

EXCEL TIMEVALUE FUNCTION

Read More: How to Use DATE Function in Excel (8 Examples)


💡 Things to Remember When Using TIMEVALUE Function

📌 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.

ERROR

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

TEXT 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).


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.


Related Articles

Prantick Bala

Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo