SOLVED

# TIMEVALUE errors between 0000 and 0059

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.

4 Replies
best response confirmed by emcguffey (Copper Contributor)
Solution

# Re: TIMEVALUE errors between 0000 and 0059

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

# Re: TIMEVALUE errors between 0000 and 0059

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.

# Re: TIMEVALUE errors between 0000 and 0059

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.

# Re: TIMEVALUE errors between 0000 and 0059

Ahh ok, thanks for the explanation.