Converting Text to Time

Question

Hi all - while importing date, my time is coming in as text - 12:56

I'm trying to convert this text to time but nothing I have tried works.

I tried =timevalue(A1,"hh:mm") with A1 being the 12:56 but that didn't work.

Tried =timevalue(A1) and that didn't work.

Tried formatting the column for "time" and that didn't work.

I can break it down to components and then reconcatenate as date but would prefer to not to do so many steps.

What am I missing?

Thanks

Answer 1

I can't duplicate your results. The formula

=TIMEVALUE(A1)

produces the Excel serial number for the time which can then be formatted as time.

Your question indicates that there is nothing else in the cell than the time as text. It would be helpful to actually see a screenshot of the data to confirm that there isn't something interfering with the formula. Also, make sure that the format is set to General or Time before entering the formula.

As an alternative you can try this formula, although if there is something keeping the TIMEVALUE formula from working it may keep this from working as well.

=TIME(TEXTBEFORE(A1,":"),TEXTAFTER(A1,":"),0)

Answer 2

I have a similar issue, I have a column where some of the hh:mm are eg 36:00 and when I run this it converts to just 12 hours not 36.