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

[dougga]

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?

[#2]

@dougga 

How are you doing this? Please provide detailed information.

[#3]

@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

[#4]

@dougga 

It's all in the support article:

https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457 

[#5]

@dougga 

Use =32768/86400 and format the result as dd:hh:mm:ss

(86400 is the number of sconds in a day)

[#6]

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.

[#7 · Accepted solution]

@dougga 

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

[#8]

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