time format change

Copper Contributor

I received a text (tab delim) file with times in it.   I opened in Excel and need to fix the times.
Some times are in h:mm and work correctly in Excel.
Other are just :mm and Excel will not see them as times no matter how I format the column.   If I retype them as 0:mm then it will work correctly.

is there any quick way to reformat the entries to get the 0 in front of the entries that are just colon minutes?

I have tried formatting the column with the time format.  - didn't work
I have tried a custom format of Use a custom time format of 00\:00 which would didn't work as expected as either since the colon is already in the column.


2 Replies
Hi. The right approach is to import the text file with Power Query. Opening the text file directly with excel will give a lot problems. There are lots of tutorials about PQ on YouTube.

Thank you @FikturFox