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…
This extracts everything to the left of the first occurrence of the colon (:). This represents the hours
This extracts the minutes by extracting 5 characters from the right of B1 (09:12) and then extracting the 2 leftmost characters of that
This extracts the 2 rightmost characters from B1 – in other words, the seconds.
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