Forum Discussion
Convert from standard notation to time does not work
JI have loaded date from a database and want to convert that data to time but it does not work. The format is now standard en looks like this. 164833. If i convert it to time then it becomes 00:00:00...
Hi,
If this notation 164833 means hhmmss, then you can use this formula to convert it to proper time format:
=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
Hope that helps
It is almost perfect but de conversion is wrong with time before noon. For example 93031 is converted to 21:03:31. But it is in de morning en not in the evening. And 84340 is converted in 12:34:40. Do you know what i am doing wrong.
Sorry, it seems that the formula has a bug!
I've fixed it as follows:
=IF(LEN(A1)=5,TIME(LEFT(A1,1),MID(A1,2,2),RIGHT(A1,2)),
TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)))
