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