PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
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.
TIME FUNCTION BECOME VERY IMPORTANT WHEN WE HAVE TO HANDLE HOUR, MINUTE OR SECONDS INDIVIDUALLY AND THEN PASS THEM AS A TIME VALUE.
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.
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.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE