How to Use Excel TIMEVALUE Function?
Excel TIMEVALUE Function returns a valid time from a text string!
TL;DR
The TIMEVALUE function converts a time written as text (like “1:30 PM“) into a decimal number between 0 and 1, where 0 = midnight and 0.5 = noon. The Syntax of the TIMEVALUE Function is =TIMEVALUE(time_text). For example, the TIMEVALUE Function returns 0.71875 as the output value for 5:15 PM input value. Find more information tutorials on our HomePage: Excel24x7.com.
TIMEVALUE Function: A Brief
The TIMEVALUE function in Excel is used to convert a time string into a decimal number that Excel recognizes as a time value. This decimal can then be formatted as a time or used in calculations. This is especially useful when your time is in text format and you want to convert it to an actual time value that Excel understands.
Note: TIMEVALUE function will return #VALUE! error, when there is an issue with your inputs. You can fix this by providing a proper time string without any hidden characters.
| Objective | Value Returned by function |
|---|---|
| Aim to return a valid decimal value from text string | TIMEVALUE Function will convert a Excel time string into a decimal value. |
TIMEVALUE Function: A Syntax
=TIMEVALUE(time_text)- time_text: A time written as text, such as “6:30 PM” or “09:15”.
The function returns a decimal number between 0 and 1:
| Decimal Value | Represents |
|---|---|
| 0 | 00:00:00 (midnight) |
| 0.5 | 12:00:00 PM (noon) |
| 0.75 | 6:00:00 PM (evening) |
Basic Examples:
In the below example, I’ve covered most of the common ways to use the TIMEVALUE function are shown using different types of input values.
| S.no | Input | Raw Output | Formatted Time |
|---|---|---|---|
| 1 | 5:15 PM | 0.71875 | 05:15 PM |
| 2 | 08:45 PM | 0.864583333 | 08:45 PM |
| 3 | 22:00 | 0.916666667 | 10:00 PM |
| 4 | 06:30 | 0.270833333 | 06:30 AM |
| 5 | 15-Feb-24 08:00 | 0.333333333 | 08:00 AM |
| 6 | 10/12/1999 9:45 AM | 0.40625 | 09:45 AM |
| 7 | 48:00 | 0 | 12:00 AM |
| 8 | 3:30:30 | 0.146180556 | 03:30 AM |
| 9 | 0:25 | 0.017361111 | 12:25 AM |
| 10 | 0:0:20 | 0.000231481 | 12:00 AM |
| 11 | 24:75 PM | #VALUE! | #VALUE! |
From the above examples, you can understand the following:
- In the first case, the function returns a decimal value because the input is a valid 12-hour time with PM.
- In the second case, the input has a leading zero, but the function ignores it and returns the correct decimal value, as it is still a valid 12-hour PM time.
- In the third case, the input is in 24-hour format, so the function returns the appropriate decimal value.
- In the fourth case, the time is entered in 12-hour format without AM/PM, so Excel assumes AM by default and returns the corresponding value.
- In the fifth case, the input includes a date and time, but the function ignores the date and returns the decimal value for the time only.
- In the sixth case, even though the input has a very old date and time, the function extracts and returns only the time part as a decimal.
- In the seventh case, the input time goes beyond 24 hours, so the function resets and returns 0, which is 12:00 AM in Excel.
- In the eighth case, the input includes hour, minutes, and seconds, so the function returns a precise decimal value.
- In the ninth case, only the minutes are entered (e.g., 0:25), and Excel interprets this as 25 minutes past midnight, returning the equivalent decimal.
- In the tenth case, the input includes only the seconds (e.g., 0:0:20), and Excel treats it as 20 seconds, returning the corresponding decimal.
- In the eleventh case, the input is not a valid time, so the TIMEVALUE function returns a #VALUE! error.
Note: In Excel, you can convert these decimal values to actual time format by entering them in a cell and applying Time formatting.
Next, select “Time” from the menu on the left. Then, under the “Type” section, choose how you want the time to be formatted and shown in the cells.
After selecting your preferred format, click the OK button to apply the changes.
| Decimal Value | Time Equivalent (hh:mm:ss) |
|---|---|
| 0.0 | 12:00:00 AM |
| 0.1 | 2:24:00 AM |
| 0.2 | 4:48:00 AM |
| 0.3 | 7:12:00 AM |
| 0.4 | 9:36:00 AM |
| 0.5 | 12:00:00 PM |
| 0.6 | 2:24:00 PM |
| 0.7 | 4:48:00 PM |
| 0.8 | 7:12:00 PM |
| 0.9 | 9:36:00 PM |
| 1.0 | 12:00:00 AM (next day) |
Using TIMEVALUE Function with other Nested Functions:
The example below explains how you can combine the TIMEVALUE function with other nested functions for common use cases.
For the given input time, 5:15 PM as the input value.
| Output | Formula Used | Description |
|---|---|---|
| 0.71875 | =TIMEVALUE(C2) | Convert Text Time to Decimal |
| 5:45 PM | =TIMEVALUE(C2) + TIME(0, 30, 0) | Add Minutes to a Time |
| 0.03125 | =TIMEVALUE(“6:00 PM”) – TIMEVALUE(C2) | Subtract Input Time from a Fixed Time |
| Afternoon | =IF(TIMEVALUE(C2)>0.5, “Afternoon”, “Morning”) | Check If Input Time Is in the Afternoon |
| 17.25 | =TIMEVALUE(C2) * 24 | Convert to Hours |
That’s it.
Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.
References:
- TimeValue function by Microsoft Learning, Retrieved on 09/04/2025: Link.
- TIMEVALUE function by Microsoft Support, Retrieved on 09/04/2025: Link.
- Why does “TimeValue()” Excel worksheet function not work on value “09:21:50.0558”? by Stackoverflow, Retrieved on 09/04/2025: Link.
- TIMEVALUE Function in Excel – Office 365 by Find Easy Solution, Retrieved on 09/04/2025: Link.
- How To Use Timevalue Function In Excel | How to calculate the hour using two given time values by The Data Millennials, Retrieved on 09/04/2025: Link.
- How to correct a #VALUE! error in the TIMEVALUE function by Microsoft Support, Retrieved on 09/04/2025: Link.
