I was recently asked why a time value divided by a number generated a #VALUE! error. The reason was because the time value was over 10,000 hours. The maximum time value recognised by Excel is 9999:59:59 (i.e. 1 second short of 10,000 hours).

In the screenshot below, B1 contains the time value 10211:09:12 – in other words, 10,211 hours, 9 minutes, 21 seconds. I don’t know what it represents – it could be the amount of time that a piece of equipment has been up-and-running, but it doesn’t really matter – it’s just a time value.

Because it is over the 10,000 hour limit, Excel treats it as text and not a numeric value. Any calculation involving B1 results in an error.

This is how I solved the problem…

B3: =LEFT(B1,FIND(“:”,B1)-1)
This extracts everything to the left of the first occurrence of the colon (:). This represents the hours

B4: =LEFT(RIGHT(B1,5),2)
This extracts the minutes by extracting 5 characters from the right of B1 (09:12) and then extracting the 2 leftmost characters of that

B5: =RIGHT(B1,2)
This extracts the 2 rightmost characters from B1 – in other words, the seconds.

B7: =B3/24+TIME(,B4,B5)
This regenerates the time but as a real numerical value that can then be manipulated as required by other formulas.

Download the sample file here