TIME Function:
Posted by Matt Bruce on August 08, 2000 11:30 AM
I am converting a number of "seconds" into hh:mm:ss - using the TIME function as follows:
=TIME(,,"mycell") - as the answer is often more
than 23:59:59 i am using the [hh]:mm:ss format.
This is fine until I get to 32768 seconds and I get the #NUM! error. 32767 seconds returns 09:06:07 - I imagine there is a limit to this function eventually but this seems very low and a
very strange number!!?
Any ideas?
Posted by Matt Bruce on August 09, 0100 2:48 AM
Michael,
That's brilliant - many thanks - kept my boss off my back with that one!
Cheers
Matt
These "pivotal points" always have some reason behind their
Posted by Michael Liu on August 08, 0100 1:30 PM
These "pivotal points" always have some reason behind their
existence. In this case, Excel is using 15 bits of memory to deal
with the TIME function. 2^15 = 32768 possible numbers that can be
represented. Since they are using 000000000000000 = 0, then
111111111111111 = 32768 - 1 = 32767, which is the last point you
notice the function to work.
Depending on what you are using this function for, I would probably
recommend embedding a mod function to deal with this limitation.
If you don't care about the hours, and just really want to know mm:ss, you
can use =TIME(,,MOD(mycell,3600))
The mod function MOD(A,B) will return the remainder of A/B
so in this case, it will return the mm:ss after "dividing out the hours".
If you do care about the hours, perhaps use:
=TIME(int(mycell/3600),mod(int(mycell/60),60),mod(mycell,60))
Hope that helps...
