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.
