SOLVED

TIMEVALUE errors between 0000 and 0059

Copper Contributor

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

Screenshot 2023-11-14 140950.png

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

@emcguffey 

=--REPLACE(TEXT(A1,"0000"),3,0,":")
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.

@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.

 

Ahh ok, thanks for the explanation.
1 best response

Accepted Solutions
best response confirmed by emcguffey (Copper Contributor)
Solution

@emcguffey 

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

View solution in original post