How to convert Text to Time in Excel with examples- Gyankosh.net

PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

HOW TO INSERT THE TIME FROM TEXT IN EXCEL

INTRODUCTION

DATES and TIME , if you are new to Excel and you have already tried using those format, I am pretty sure that you must have felt panicky at some sort of time.

It is quite obvious because when we don’t know the exact working of any system or process, we try to use it just by hit and trial method. It works sometimes and sometimes not.

Here we are talking about Dates and Time in Excel. These are the tricky formats which we need frequently in our reports or charts.

Many times, we need to do operations on them. We need to put them in the conditions to trigger some event which makes it very important for us to learn the exact procedures to perform a task concerned with the dates and time.

In this article we would learn different tricks and methods to handle and manipulate Dates and Time formats so that they don’t mess up with our reports.

One of the problem is when we need to get the time from the given text.

BEFORE READING THIS ARTICLE , IT IS REQUESTED TO VISIT THE PART I WHICH DISCUSSES THE CONCEPT OF THE DATE AND TIME IN EXCEL FOR BETTER UNDERSTANDING.CLICK HERE TO VISIT.

In this article we would learn to insert the time in excel from the given text.

INSERT THE TIME FROM TEXT IN EXCEL

CONCEPT:

Just like the date, we can insert the time from the text too. For the date we have the DATE FUNCTION and similarly for the time we have a TIME FUNCTION.

SYNTAX for the TIME FUNCTION is

TIME( HOUR, MINUTE, SECONDS).

The arguments are self explanatory. The values go as numerals.[ numbers].

If the format is General, it’ll be converted into Time.

TIME FUNCTION BECOME VERY IMPORTANT WHEN WE HAVE TO HANDLE HOUR, MINUTE OR SECONDS INDIVIDUALLY AND THEN PASS THEM AS A TIME VALUE.

The TIME FUNCTION. works in such a way that it adjusts the wrong values itself. For example if you put 61 minutes, it’ll increase an hour and make the 61 minutes as 1 minutes.
Let us try few examples to see how we can use TIME FUNCTION..

EXAMPLE 1: CONVERT THE HOURS, MINUTES AND SECONDS INTO TIME

Suppose we have been given the hours, minutes and seconds as text. Even if we try changing the format, it won’t convert it to time but we can do it using the TIME FUNCTION.

STEPS TO CONVERT HOURS, MINUTES AND SECONDS INTO TIME.

Select the cell where the output is needed.
Enter the formula =TIME(cell containing hours value/hours, cell containing minutes value/minutes, cell containing seconds value/seconds). For our example, we have three cells containing 21, 23 and 12 as hours, minutes and seconds. So the formula used is =TIME(S3,T3,U3)
The result will appear. Our example gave the result as 9:23 PM. We can change the format and it’ll show seconds too or we can use TEXT function for setting the format.

EXAMPLE 2: CREATING TIME FROM TEXT.

This is a specific case when we have copied any data from other source and it behaves as TEXT.

Many times we import or copy the data from other sources and all the imported data behaves as Text. It means that we won’t be able to apply any kind of calculations on that particular data as TEXT won’t respond to any calculations.

Let us take an example to understand the same.

We have the following scenario. We copied a list of dates from somewhere which are in the text format.

PLANNING FOR THE SOLUTION:

Now, if we try to sort this problem out by changing the format, it is not going to work.

But we can make use of TIME FUNCTION and separate the hour, minute and seconds parts using the LEFT, RIGHT , MID functions and pass the arguments to TIME FUNCTION. It will work as the data will be converted to time.

STEPS TO CONVERT THE TEXT INTO TIME:

The table is already shown and the first entry is in the cell AB9. We will create a formula for this and then drag down the formula.
Place the cursor in the cell where we want the result .
Enter the following formula.
=TIME(IF(LEN(AB9)=5,LEFT(AB9,2),LEFT(AB9,1)),MID(AB9,LEN(AB9)-4,2),RIGHT(AB9,2))
The formula would create the Time from the given time in text.
Drag down the formula to fill all the rows of the output column.

EXPLANATION:

We are simply taking out the text snippets and passing it to the TIME FUNCTION.

Let us try to understand the formula.

=TIME(IF(LEN(AB9)=5,LEFT(AB9,2),LEFT(AB9,1)),MID(AB9,LEN(AB9)-4,2),RIGHT(AB9,2))

The formula explanation from the left.

The outer function is the TIME FUNCTION which takes the three inputs as HOUR, MINUTE and SECONDS.

We extract the HOUR from the LEFT FUNCTION. Now we can check our data that we need to decide whether we ‘ll take the one digit or two. For this case we put the IF condition which will act as per the total number of LENGTH of the string.

The second input is the MINUTES, which we extract with the help of MID FUNCTION. The MID FUNCTION starts from the length -4. The number of characters to the right are fixed but from the left it is not fixed due to the number of digits in the HOUR FIELD. So we extract it from the right and subtract the fixed position which is 4 and extract 2 letters.

The third and final extraction is easier so we use the RIGHT FUNCTION and get the last two letters.

The output is correct. The text time are converted into TIME VALUES WITH CORRECT FORMAT.

THE SAME PROBLEM CAN BE SOLVED USING THE TEXT FUNCTION TOO. BUT THE ABOVE METHOD IS MENTIONED INTENTIONALLY AS IT GIVES US MUCH MORE FLEXIBILITY AND COVERS OTHER SOLUTIONS TOO.

CONVERTING THE TEXT TO TIME USING TEXT FUNCTION:

We can solve this problem using TEXT FUNCTION too.

STEPS TO CONVERT TEXT TO TIME USING TEXT FUNCTION.

Select the cell where output is needed.
Use the function =TEXT(VALUE, “HH:MM:SS”)
The output will be in TIME format.

Contents