Formula or Macro to change fromating from text to time

[DarrenF]

Hello,

I have cell J5 00:02:54 with text formatting that I'm trying to convert to time. Since the report is delivered to me in text, I’m unable to make any changes anddon’t have the ability to request to have the formatting changed. Is there a formula or macro that can convertit to time instead of text?

 

[Marcelo Branco]

Try
Select the cells
Data > Text-to-columns > Finish

M.

 

[DarrenF]

thank you that worked!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[DarrenF]

Now I have one showing 04:10:13:48. It's Days, Hours, Minutes, Seconds. Not sure why anyone would do it this way, is it possible to break it down to Hours, Minutes, Seconds and convert to time?

 

[DarrenF]

I tried =((MID(I100,1,2)*24+MID(I100,4,2))*60+MID(I100,7,2))*60+MID(I100,10,2) but not getting any results.