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. TIMEVALUE function time string decimal number time value 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. Note: #VALUE! error 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. 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. Objective

Value Returned by function Objective Objective Value Returned by function 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. Aim to return a valid decimal value from text string Aim to return a valid decimal value from text string TIMEVALUE Function will convert a Excel time string into a decimal value. TIMEVALUE Function will convert a Excel time string into a decimal value. TIMEVALUE Function: A Syntax =TIMEVALUE(time_text) =TIMEVALUE(time_text) time_text: A time written as text, such as “6:30 PM” or “09:15”. time_text: A time written as text, such as “6:30 PM” or “09:15”. time_text 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) Decimal Value

Represents



0

00:00:00 (midnight)



0.5

12:00:00 PM (noon)



0.75

6:00:00 PM (evening) Decimal Value

Represents Decimal Value Decimal Value Represents Represents 0

00:00:00 (midnight) 0 0 00:00:00 (midnight) 00:00:00 (midnight) 00:00:00 0.5

12:00:00 PM (noon) 0.5 0.5 12:00:00 PM (noon) 12:00:00 PM (noon) 12:00:00 PM 0.75

6:00:00 PM (evening) 0.75 0.75 6:00:00 PM (evening) 6:00:00 PM (evening) 6:00:00 PM 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. TIMEVALUE 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! 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! S.no

Input

Raw Output

Formatted Time S.no S.no Input Input Raw Output Raw Output Formatted Time Formatted Time 1

5:15 PM

0.71875

05:15 PM 1 1 5:15 PM 5:15 PM 0.71875 0.71875 05:15 PM 05:15 PM 2

08:45 PM

0.864583333

08:45 PM 2 2 08:45 PM 08:45 PM 0.864583333 0.864583333 08:45 PM 08:45 PM 3

22:00

0.916666667

10:00 PM 3 3 22:00 22:00 0.916666667 0.916666667 10:00 PM 10:00 PM 4

06:30

0.270833333

06:30 AM 4 4 06:30 06:30 0.270833333 0.270833333 06:30 AM 06:30 AM 5

15-Feb-24 08:00

0.333333333

08:00 AM 5 5 15-Feb-24 08:00 15-Feb-24 08:00 0.333333333 0.333333333 08:00 AM 08:00 AM 6

10/12/1999 9:45 AM

0.40625

09:45 AM 6 6 10/12/1999 9:45 AM 10/12/1999 9:45 AM 0.40625 0.40625 09:45 AM 09:45 AM 7

48:00

0

12:00 AM 7 7 48:00 48:00 0 0 12:00 AM 12:00 AM 8

3:30:30

0.146180556

03:30 AM 8 8 3:30:30 3:30:30 0.146180556 0.146180556 03:30 AM 03:30 AM 9

0:25

0.017361111

12:25 AM 9 9 0:25 0:25 0.017361111 0.017361111 12:25 AM 12:25 AM 10

0:0:20

0.000231481

12:00 AM 10 10 0:0:20 0:0:20 0.000231481 0.000231481 12:00 AM 12:00 AM 11

24:75 PM

#VALUE!

#VALUE! 11 11 24:75 PM 24:75 PM #VALUE! #VALUE! #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. In the first case, the function returns a decimal value because the input is a valid 12-hour time with PM. first case decimal value 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. second case leading zero ignores it decimal value 12-hour PM time In the third case, the input is in 24-hour format, so the function returns the appropriate decimal value. third case 24-hour format 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. fourth case 12-hour format without AM/PM AM by default 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. fifth case date and time ignores the date 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. sixth case very old date and time time part 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. seventh case beyond 24 hours resets 0 12:00 AM In the eighth case, the input includes hour, minutes, and seconds, so the function returns a precise decimal value. eighth case hour, minutes, and seconds 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. ninth case minutes 25 minutes past midnight 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. tenth case seconds 20 seconds In the eleventh case, the input is not a valid time, so the TIMEVALUE function returns a #VALUE! error. eleventh case not a valid time TIMEVALUE #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. Note: decimal values actual time format 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. “Time” “Type” formatted and shown After selecting your preferred format, click the OK button to apply the changes. OK 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) 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) Decimal Value

Time Equivalent (hh:mm:ss) Decimal Value Decimal Value Time Equivalent (hh:mm:ss) Time Equivalent (hh:mm:ss) 0.0

12:00:00 AM 0.0 0.0 12:00:00 AM 12:00:00 AM 0.1

2:24:00 AM 0.1 0.1 2:24:00 AM 2:24:00 AM 0.2

4:48:00 AM 0.2 0.2 4:48:00 AM 4:48:00 AM 0.3

7:12:00 AM 0.3 0.3 7:12:00 AM 7:12:00 AM 0.4 0.4 0.4 0.5

12:00:00 PM 0.5 0.5 12:00:00 PM 12:00:00 PM 0.6

2:24:00 PM 0.6 0.6 2:24:00 PM 2:24:00 PM 0.7

4:48:00 PM 0.7 0.7 4:48:00 PM 4:48:00 PM 0.8

7:12:00 PM 0.8 0.8 7:12:00 PM 7:12:00 PM 0.9

9:36:00 PM 0.9 0.9 9:36:00 PM 9:36:00 PM 1.0

12:00:00 AM (next day) 1.0 1.0 12:00:00 AM (next day) 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. combine the TIMEVALUE function nested functions 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 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 Output

Formula Used

Description Output Output Formula Used Formula Used Description Description 0.71875

=TIMEVALUE(C2)

Convert Text Time to Decimal 0.71875 0.71875 =TIMEVALUE(C2) =TIMEVALUE(C2) Convert Text Time to Decimal Convert Text Time to Decimal 5:45 PM

=TIMEVALUE(C2) + TIME(0, 30, 0)

Add Minutes to a Time 5:45 PM 5:45 PM =TIMEVALUE(C2) + TIME(0, 30, 0) =TIMEVALUE(C2) + TIME(0, 30, 0) Add Minutes to a Time Add Minutes to a Time 0.03125

=TIMEVALUE(“6:00 PM”) – TIMEVALUE(C2)

Subtract Input Time from a Fixed Time 0.03125 0.03125 =TIMEVALUE(“6:00 PM”) – TIMEVALUE(C2) =TIMEVALUE(“6:00 PM”) – TIMEVALUE(C2) Subtract Input Time from a Fixed Time Subtract Input Time from a Fixed Time Afternoon

=IF(TIMEVALUE(C2)>0.5, “Afternoon”, “Morning”)

Check If Input Time Is in the Afternoon Afternoon Afternoon =IF(TIMEVALUE(C2)>0.5, “Afternoon”, “Morning”) =IF(TIMEVALUE(C2)>0.5, “Afternoon”, “Morning”) Check If Input Time Is in the Afternoon Check If Input Time Is in the Afternoon 17.25

=TIMEVALUE(C2) * 24

Convert to Hours 17.25 17.25 =TIMEVALUE(C2) * 24 =TIMEVALUE(C2) * 24 Convert to Hours Convert to Hours That’s it. This tutorial is originally published on How to Use Excel TIMEVALUE Function? How to Use Excel TIMEVALUE Function?

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

How to Use Excel WEEKDAY Function?

Using Excel's TIME Function to Return Time From Hour, Minute, Seconds!

View Website Here

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

Here's How to Convert Time into Decimal Value Using Excel's TIMEVALUE Function

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Excel Formulas for Assigning Categories by Value Range Using Functions!

Excel Formulas for Assigning Categories by Value Range Using Functions!

5 Excel Features That Can Grow Your Net Worth in No Time

Excel Hack: How to Get Currency Data in Excel

How to Import Crypto APIs into Excel

Excel Formulas for Assigning Categories by Value Range Using Functions!

Excel Formulas for Assigning Categories by Value Range Using Functions!

5 Excel Features That Can Grow Your Net Worth in No Time

Excel Hack: How to Get Currency Data in Excel

How to Import Crypto APIs into Excel

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps