Convert from standard notation to time does not work

[Gijsl]

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. Does anyone know what to do. In this format i can not use it for proces mining. 

[#2]

@Gijsl

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 

[#3]

Hello @Gijsl,

Are you looking to convert 164833 into the format 16:48:33?

Sincerely,

PReagan

[#4]

@Gijsl  so if you have a serial date (just a number), then use this formula in a cell (like D8) '=TEXT(C8,"m/d/yyyy")'

does that help?

[#5]

whoops, missed the boat on that one, ignore this:

[#6]

@PReagan 

Yes that is right. That is what i am trying to achive.  

[#7]

@Haytham Amairah 

het werkt👍🏻  It works. Tnx. 

[#8]

@Haytham Amairah 

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. 

[#9]

@Gijsl

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)))

[#10]

@Gijsl

Treating the original as a number, convert first to correctly formatted text, then back to a time value

= TIMEVALUE( TEXT(number, "00\:00\:00") ) 

[#11]

@Haytham Amairah

it works. Tnx.