Timevalue function not working for unrecognized time format-CodePudding

I have a bunch of time data that's formated weirdly, ranging from numbers 100 (representing 1 AM, or 01.00.00) to 2359 (representing 11.59PM, or 23.59.00).

I have been trying to use the TIMEVALUE() function to convert these data, but it just returns #Value?, I guess because the time format 'HHMM' is not recognized without the ' : ' separating them?

What I'd like is to convert it to the HH:MM:SS format, where the SS would automatically be zero.

CodePudding user response：

Use REPLACE:

=--REPLACE(A1,LEN(A1)-1,0,":")

Then format it as desired.

CodePudding user response：

If your text values are consistent, this can be carried out by using a formula that splits the value by the number of characters and then recombines the split values into a time value.

All formulas assume that your weirdly formatted text value is in cell B2

=IF(LEN(B2)>3,LEFT(B2,2),LEFT(B2,1))

This formula works out the first (hour) element of a time, if the text string is greater than three characters it will take the first 2 characters of the string (23), if its less than 3 characters it will only take the first character (1)

=RIGHT(B2,2)

This formula takes the second (minute) element of the time.

=TIME(C2,D2,0)

Finally this formula converts the two text elements into a string

C2 = The cell with formula 1 in it

D2 = The cell with formula 2 in it

This could all be written as the following formula if needed

=TIME(IF(LEN(B2)>3,LEFT(B2,2),LEFT(B2,1)),RIGHT(B2,2),0)