In this post we will talk about how we could insert/concatenate colon “:” into numbers in excel to make it look like time value.
Assume we have data in column A which are actually time values but the colon has been wiped out from each of them and we want to make the values look like time value by inserting colon “:” in each of them.
We will see a simple excel formula called TEXT with the help of which we will be able to insert/concatenate colon into the numbers to make them look like time values.
Enter the formula =TEXT(A2,”#0\:00″) in the cell B2, so in this TEXT formula we are just changing the format of the numbers to make them look like they are time values without actually concatenating colon between them.
Now drag the formula in the cell B2 till the place you have data available in column A.
As you could see in the formula , the second argument to the text function is the format in which you want the representation and I have provided the format string “#0\:00”) which will do the job of displaying the numbers as time values by inserting colons in between them.
You could also use the formula =–TEXT(A1,”#0\:00″) to make it a time value and change the format to hh:mm
Hope this helped.
