TIMEVALUE errors between 0000 and 0059

[emcguffey]

Below is a snapshot of a very large data set I am working with:

When using the following TIMEVALUE formula I always get value errors from times between 0000 and 0059 but it works just fine between 0100 and 2359.

     =TIMEVALUE(LEFT(C29060,LEN(C29060)-2)&":"&RIGHT(C29060,2))

The Time column is in a "custom" format of 0000 so I can get the 4 digits needed for the time conversion.

I would appreciate if anyone has a solution to this.

[#2 · Accepted solution]

@emcguffey 

=--REPLACE(TEXT(A1,"0000"),3,0,":")

[#3]

Thank you for this. Do you mind explaining why this works and the other function I used didn't? Just trying to better understand what is happening.

[#4]

@emcguffey 

The four digits only show because of the custom format.

The cell content for 58 minutes is 58 and not 0058.

So LEFT() returns "". Together with RIGHT() the result is :58.

And TIMEVALUE() will convert this because of the missing 0.

[#5]

Ahh ok, thanks for the explanation.