This content originally appeared on HackerNoon and was authored by Excel24x7
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.
\
\ Basic example of using TIMEVALUE Function with common scenarios.
| 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 | | | 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) |
\ The equivalent values of time and its decimal value.
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.
\ Using TIMEVALUE function with other Nested functions
\ 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. This tutorial is originally published on How to Use Excel TIMEVALUE Function?
This content originally appeared on HackerNoon and was authored by Excel24x7
Excel24x7 | Sciencx (2025-04-12T08:53:49+00:00) Here’s How to Convert Time into Decimal Value Using Excel’s TIMEVALUE Function. Retrieved from https://www.scien.cx/2025/04/12/heres-how-to-convert-time-into-decimal-value-using-excels-timevalue-function/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.