Error #NUM when converting decimal to dd:hh:mm:ss

New Contributor

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


How are you doing this? Please provide detailed information.

@Hans Vogelaar  I can repro it by doing this in a cell:

This works


This doesn't


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)

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.
best response confirmed by dougga (New Contributor)


That's only with formula, format itself doesn't change the value


I think I was making it harder than it needed to be. Thanks.