Forum Discussion
Solved
Unable to format Time
Unable to format time
Another variant is
=--REPLACE(REPLACE(TEXT(C1,"000000"),LEN(TEXT(C1,"000000"))-1,0,":"),LEN(TEXT(C1,"000000"))-3,0,":")and apply Time format.
Hi Eklavya
I have attached a solution to get the correct time for you, you should have perhaps explained better the format of the time column as it would have been easier to work out. you will see that it all depends on the length of the time values. I have used IF statements to decide the number of digits and apply the appropriate format.
=IF(LEN(C1)=1,TIME(0,0,0),IF(LEN(C1)=3,TIME(0,MID(C1,1,1),0), IF(LEN(C1)=4,TIME(0,LEFT(C1,2),0),IF(LEN(C1)=5,TIME(LEFT(C1,1),MID(C1,2,2),0),TIME(LEFT(C1,2),MID(C1,3,2),0)))))
Richard