While working with date and time in Excel, we may sometimes feel a need of performing arithmetical calculations on the time. Unfortunately, excel cannot perform any calculations directly on the time format (HH:MM:SS). In such a scenario, the TIMEVALUE function in excel comes into its use.
The TIMEVALUE formula in excel is useful to convert the time in HH:MM:SS format into its decimal numerical value. This blog will explain to you the usage of the excel TIMEVALUE function.
- When to Use TIMEVALUE Function Of Excel
- Syntax and Arguments
- Points To Remember About TIMEVALUE Formula of Excel
- Examples To Learn TIMEVALUE Function of Excel
- Bonus Section
Here we go 😎
When to Use TIMEVALUE Function Of Excel
The expression TIMEVALUE represents “Numerical value of time”. Sometimes, the time in excel is recognized by it in text format. The TIMEVALUE function in excel extracts the numerical value of time from this text value.
Therefore, the formula returns the decimal number. The numerical value ranges between 0.00 to 0.99999.
The excel TIMEVALUE formula is categorized under the date/time functions of excel.
Syntax and Arguments
The below point explain the required function argument for the TIMEVALUE formula.
- time_text – In this argument, specify the text value of the time. It could be a cell reference or the actual time enclosed in double quotes.
The function can also extract the time when both date and time are entered as the time_text argument.
Points To Remember About TIMEVALUE Formula of Excel
One must keep the following points in mind before using the excel TIMEVALUE function.
- The time in excel has its numerical value. This numerical value starts from 12:00:00 AM as 0 and ends at 11:59:59 PM as 0.99999 (more precise value is 0.999988425925926).
- To check whether the time is the text format or not, use the ISTEXT formula. The function returns boolean value TRUE or FALSE based on the cell format. Alternatively, you may also check the alighment of the time value in the cell. By default, the time in text format are left aligned, while the time format is right aligned.
- The TIME function extracts the numerical value of time when there is date and time value in the same cell. For example, when we have the value “10-10-2021 12:01 AM” as TIMEVALUE input argument, the formula will return 0.000694 viz. the numerical value of the time (ignoring the date).
- When you pass the time in time format (and not text format), the TIMEVALUE function returns a #VALUE! error code.
Examples To Learn TIMEVALUE Function of Excel
In this section, let us learn some practical examples to understand the working of the Excel TIMEVALUE Formula.
Ex. 1 – Passing Time Directly as Function Argument in TIMEVALUE Formula
The numerical values of time are more useful than any other format of time. This is because arithmetical calculations are easy to perform on numbers.
to find the number value of time in excel, simply enter the time in HH:MM:SS format within double-quotes as the function argument.
As a result, the formula has returned 0.531840278. The value 0.531840278 is numerical value of the time 12:51:45 AM.
If you do not enclose the time within double-quotes, excel will consider it as a cell reference.
Ex. 2 – Passing Different Arguments to TIMEVALUE Formula
The below image contains time and/or date in different formats in column A.
Firstly, let us check which of these values are in text format using the ISTEXT formula.
The formula TRUE for those values which are in text format.
Now, use the following TIMEVALUE formula.
As a result, the formula returns the time value for the times which are in text format.
We can copy the formula for the rest of the cells using the autofill handle tool.
The following observations are worth noting:
- The function returns #VALUE! error code in excel for the cells A4 and A6 because of the non-text value of time.
- In the last record, which contained merged date and time, the function successfully extracted the numerical value of time from it, while ignoring date portion.
Ex. 3 – Performing Calculation on Time Using Excel TIMEVALUE Formula
Let us find the time period between the time intervals using the TIMEVALUE function.
Use the following TIMEVALUE formula to get the difference of time in the number format.
As a result, the formula has returned 0.646909722.
Explanation – In the first TIMEVALUE function we have passed cell A2 and in the second TIMEVALUE Function we have passed B2. The formula subtracted the numerical values of time contained in cells A2 and B2. The difference comes out to 0.646909722.
Let us extract the number of hours, minutes, and seconds from the numerical value of the difference contained in cell C2. Use this formula:
.=HOUR(C2)&” hour”&MINUTE(C2)&” minutes “&SECOND(C2)&” seconds”
The formula returns 15 Hours 31 Minutes 33 Seconds.
Explanation – In the above formula, we have extracted the number of hours, minutes, and seconds from cell C2 by using the HOUR, MINUTE, and SECOND Functions of Excel.
Thank you for reading 😉