Jan 04 2022 07:20 PM
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?
Jan 05 2022 01:19 AM
How are you doing this? Please provide detailed information.
Jan 05 2022 07:13 AM
@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
Jan 05 2022 08:17 AM
It's all in the support article:
https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457
Jan 05 2022 08:22 AM
Use =32768/86400 and format the result as dd:hh:mm:ss
(86400 is the number of sconds in a day)
Jan 05 2022 11:05 AM
Jan 05 2022 11:20 AM
SolutionJan 05 2022 05:00 PM
Jan 05 2022 11:20 AM
SolutionThat's only with formula, format itself doesn't change the value