Table of Contents
- INTRODUCTION
- PURPOSE OF TIMEVALUE FUNCTION IN EXCEL
- PREREQUISITES TO LEARN TIMEVALUE FUNCTION
- SYNTAX OF TIMEVALUE FUNCTION
- EXAMPLE: TIMEVALUE FUNCTION IN EXCEL
- EXPLANATION:
- FAQs
- HOW TO PASS TIME AS TEXT DIRECTLY?
INTRODUCTION
TIMEVALUE FUNCTION returns the decimal equivalent of given TIME [given as Text] in EXCEL.
TIMEVALUE FUNCTION is a function under the DATE AND TIME FUNCTIONS category in Excel.
The function is very simple to understand and does a small job but is extremely helpful in sorting out many tricky and tough problems in excel.
It is very helpful in Excel especially when we are working with the dates and time in Excel. The function is quite helpful in the conversion of the different time values in Text into time easily.
In this article we would learn about the purpose, formula, syntax and examples about the TIMEVALUE function in Excel.
PURPOSE OF TIMEVALUE FUNCTION IN EXCEL
TIMEVALUE FUNCTION returns or finds out the decimal equivalent of any TIME which is represented as Text in Excel.
AN EXCERPT FROM ANOTHER ARTICLE MANIPULATING DATE AND TIME IN EXCEL which is helpful in understanding about this.
As DATE is treated as the serial number, time is taken as the decimal portion of the same serial number.
We know that there are 24*60*60 seconds in a day which comes out to be 86400. So we will divide the decimal part in 86400 portions.
So one second is equal to 1/86400 which comes out to be around 0.00001157 .
One minute is equal to 1/(24*60) which comes out to be around 0.00069444.
So we would try to find out this decimal number from the given date with the use of TIMEVALUE FUNCTION.
PREREQUISITES TO LEARN TIMEVALUE FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- If we know the way Excel handles the TIME, the function becomes a bit easier to learn.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel?
SYNTAX OF TIMEVALUE FUNCTION
The Syntax for the TIMEVALUE FUNCTION in Excel is
=TIMEVALUE (TIME AS TEXT)
TIME AS TEXT is the given value of TIME expressed as text which is to be converted into decimal equivalent of the time.After the decimal equivalent is present, we can easily get the time by changing the cell format to time and can perform all the calculation related to TIME in Excel.
EXAMPLE: TIMEVALUE FUNCTION IN EXCEL
DATA SAMPLE
For the sake of understanding, let us try TIME VALUE FUNCTION for some examples.
The following data is simple to understand.
Four examples are taken in which time is taken as in TEXT FORMAT.
| EXAMPLE | TIME in text |
| 1 | 10:15 |
| 2 | 12:45 |
| 3 | 18:00 |
| 4 | 06:00 |
STEPS TO USE TIMEVALUE FUNCTION
Following table gives the description of the formula usage.
The TIME VALUE COLUMN is our result which shows the DECIMAL VALUES for the time given in TIME IN TEXT column. The lower two values of the example 3 and 4 are shown as the time in our result column i.e. TIME VALUE COLUMN as the format of the cells was changed to TIME after the decimal calculation just to show the further processing.
The general steps are
- Select the cell where we want to find out the result.
- Put the given formula.
- The general format of the function is
- =TIMEVALUE(DIRECT TIME AS TEXT/ CELL CONTAINING THE TIME AS TEXT)
The result and the formula used for individual examples are given in the table below.
| EXAMPLE | TIME in text | TIME VALUE | FORMULA USED |
| 1 | 10:15 | 0.42708333 | =TIMEVALUE(C4) |
| 2 | 12:45 | 0.53125 | =TIMEVALUE(C5) |
| 3 | 18:00 | 6:00:00 PM | =TIMEVALUE(C6) |
| 4 | 06:00 | 6:00:00 AM | =TIMEVALUE(C7) |
EXPLANATION:
The explanation is very simple.
We take the value of the time as TEXT and use the function on the value.
The value goes as the single parameter and the result is shown.
In this way we can make use of this important function.
FAQs
HOW TO PASS TIME AS TEXT DIRECTLY?
Passing the time as text simply means that the time should behave as text and not time.
If we are using directly , use the time in the way shown below.
=TIMEVALUE(“12:30”)
If you are
HOW TO CHANGE THE FORMAT OF THE CELL TO TEXT?
If we are not using the direct values, we can change the format of the cell to TEXT.
Follow the link to change the format to TEXT.