SOLVED

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

@dougga 

How are you doing this? Please provide detailed information.

@Hans Vogelaar  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

@dougga 

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)
Solution

@dougga 

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

image.png

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