IF statement HELP!!!!?

Question

=TIME(LEFT(IF(LEN(P249) = 5, "0" &P249, P249), 2),MID(IF(LEN(P249) = 5, "0" & P249, P249), 3,2),RIGHT(IF(LEN(P249)=5, "0" & P249, P249),2))

This is used to extract a 6 digit number that represents a millitary time HHMMSS (hours, minutes, second)

for example: if the cell says 23 that is means 12:00:23 AM and the statement should return 12:00 AM
if the cell says 172359 that means 5:23:59 and the statement should return 5:23 PM

something here is wrong and I can't figure it out.
ANY GENIUSES OUT THERE?

Answer 1

If the length of the string is not 5, there is no provisionin the MID() function to extract any more than 3 characters. When there are less than 5 characters you get an wrong answer, when there are less than 3 you get a #VALUE error.

You have to provide a nested IF for the MID statement to allow for fewer than 3 characters.

Other than that it worked for me for normal length strings (I tried a few of different military times).

.

Answer 2

The formula you have works for text strings of 5 or 6 characters in length. It does not work for other size strings. Also, make sure that P249 is a string and not a number.