Forum Discussion
Error #NUM when converting decimal to dd:hh:mm:ss
I am trying to convert a decimal to dd:hh:mm:ss format but get an #NUM error. It works until I hit an upper limit.
I figure out the issue but don't know how to fix it.
When I convert a decimal to dd:hh:mm:ss it has a limit 32768 (limit of an INT). If I convert/format this number: 32767 - it works. If I convert 32768 I get a #NUM error.
How can I use a value greater than the INT limits in the time(,,32768) or Format Cell dd:hh:mm:ss and not get the #NUM error?
7 Replies
- Getting closer, but maybe just a bit more explanation will help. And it may be the =TIME formula is not the right use here.
My situation is I have a number in seconds, and it may range from a low number of 30 to 40 seconds but can also get in the multiple hour ranges of tens of thousands of seconds.
So regardless of the number of seconds I just want to display that in 00:00:00:00 format.
I can probably write my own formula and concatenate the result, but that seems overkill.
Thanks for all the feedback so far.- I think I was making it harder than it needed to be. Thanks.
How are you doing this? Please provide detailed information.
HansVogelaar I can repro it by doing this in a cell:
This works
=TIME(,,32767)This doesn't
=TIME(,,32768)I am converting seconds to a time span. I am trying for 00:00:00 (h:mm:sec). The first one above works the second I get #NUM
Use =32768/86400 and format the result as dd:hh:mm:ss
(86400 is the number of sconds in a day)
