TIMEVALUE Function
It converts the text of the time into a decimal form, making it recognizable as a time value in Excel.
What is the TIMEVALUE Function?
TIMEVALUE is a date and time function in Excel that converts the text of the time and returns the time value into a decimal value. The values range anywhere from 0 (12:00:00 am or 0:00:00) to 0.9998426 (11:59:59 pm or 23:59:59).
Time in Excel is stored as a numerical value, meaning that time is not recognized in the text. The TIMEVALUE function converts the text of the time into a decimal form, making it recognizable as a time value in Excel.
It’s useful for financial analysts in determining time calculations, hourly wages, and converting time from other text sources to a serial number representing the time.
The syntax for this function is
=TIMEVALUE(time_text)
where
- time_text = a text string that represents the time.
When you use this function, you must input the values as “Hour:Minute:Second," with AM or PM listed after. Once you’ve imputed the time into your parentheses, you should get the serial number of that time.
NOTE
The maximum value returned by TIMEVALUE is less than 1 because, like a clock, the hours reset every 24 hours.
Key Takeaways
- TIMEVALUE is used to break down the components of the text value into a valid time in Excel.
- If you were to input a time value into Excel, you must put quotations around them; otherwise, you’ll get either a #SPILL! Error (if you input 5:30, for example) or a #NAME? Error (if you put 5:30 PM, for instance).
- If the time_text cannot be recognized as a valid Excel time (for instance, if you put just the number 5 in the parentheses), it will return with a #VALUE! Error.
- The function returns any number within the range of 0, which represents 12:00:00 AM, to 0.99988426, which is 11:59:59 PM.
Examples of TIMEVALUE
In this section, we will go over how to use the TIMEVALUE function by providing a step-by-step guide and provide more examples using this function.
It’s very easy to use. All that’s required is to put the time into the “time_text” section. For example, suppose you wanted to find out what the function would return if you put 12:20 pm in the syntax. You must put “12:20:00 PM” in the syntax (with the quotations).
Once you’ve imputed your required arguments into the function, you get 0.51388889 as your answer.
Now that we understand how to use TIMEVALUE, let’s look at some more examples using this feature. Below is a screenshot of random examples using the TIMEVALUE function.
Let’s say you wanted to determine the value of 6:30 AM if we were to convert it into decimal form. Using the TIMEVALUE function, we input “6:30:00 AM” into the syntax (with the quotations included) and get 0.270833 as our answer.
Next, suppose you wanted to determine the value of 2:50 PM and 30 seconds. By using the same method we used in the first example to find the value of 6:30 AM, we input “2:50:30 PM” into the parentheses and got 0.618403 back.
Then, you want to see what would happen if you experimented with time values from other regions. Using 0:06:26 (12:06:23 AM) as our reference, we get 0.004433 as our answer.
Finally, you want to determine the time value of 18:27:00 (6:27:00 PM) in decimal form. By inputting “18:27:00” into our formula, we get 0.76875.
Timevalue Function vs. Datevalue Function
Now that we’ve gotten how TIMEVALUE works, you’re probably wondering if there is a similar function to this, but with dates. The answer is yes; it’s called the DATEVALUE function.
DATEVALUE is a function in Excel that converts the date into a serial number recognized as a date.
The syntax for Datevalue is
=DATEVALUE(date_text)
where
date_text = the referenced date in the text string.
| TIMEVALUE | DATEVALUE |
|---|---|
| Converts the time string from text into date/time format | Converts the date string from text into date/time format. |
| It ignores any date information in the string. | It ignores any time information in the string. |
It’s very easy to use, as the only thing you have to do is insert a date (in numeric form) into the syntax, and it returns the serial number of that date.
Let’s use January 18th, 2000, as our reference date. Using the DATEVALUE function, we put the date “01/18/2000” into parentheses (with the quotations included).
Now that we’ve inputted our date into the required field, we hit enter and returned with 36543 as our answer, representing January 18th, 2000.
Let’s look at some more examples using Datevalue. Below is a screenshot of random examples using Datevalue.
Let’s say you wanted to determine what the value of June 30th, 2020, would be. Using the DATEVALUE function, we input “06/30/2020” into the parentheses (with the quotations included) to get 44012 as our answer.
Next, suppose you wanted to determine the serial number of July 18th, 2020. By using the same steps we did to find the serial number for June 30th, 2022, we input “07/18/2020” for our date_text and got 44030.
Then, you want to determine the serial number for the same date but a year later. We input “07/18/2021” in our date_text section and got 44395.
Finally, you want to know the serial number of a date in a later year and a different month to see how big the difference is between serial numbers. Using “08/30/2022” as our example, we return with 44803 as the answer.
As we can see from the two different functions, their methods are similar to one another. Still, the conclusions differ as TIMEVALUE returns a decimal number as the answer, while DATEVALUE returns a full number.
or Want to Sign up with your social account?