Excel 2007 : Converting 32768 Seconds to [hh]:mm:ss

**jygoehring** · 08-04-2011, 01:38 PM

I have made a table which calculates the time difference between different cities using coordinates. I basically take the distance of the hypotenuse and convert it to seconds (based on my rates). I insert those seconds into the time formula like this: TIME(0,0, #). Then I change the cell format to show the time in [hh]:mm:ss. Unfortunately, the times will not convert to [hh]:mm:ss past 32768 seconds. It goes up to just over 9 hours. So, if I have a city 10 hours away, it is showing up as #NUM!. If it were perfect, I'd love for it to just count as many possible hours in the distance, like, 123:22:31 (123 hours, 22 minutes, 31 seconds).

Any help is greatly appreciated. Thanks!

**shg** · 08-04-2011, 01:45 PM

Welcome to the forum.

=A1/86400 and format as you wish.

**jygoehring** · 08-04-2011, 02:15 PM

Here is one example:

=TIME(0,0,ROUNDDOWN((SQRT((((ABS($D5-$D$4))^2)+((ABS($E5-$E$4))^2))))*$AC$8*$AC$14,0))

You can see that I'm rounding down the hypotenuse distance between 2 sets of coordinates and multiplying it by my own rates.

How do I set it up if my 2 sets of coordinates are like 1,1 and 400, 400? In other words, how do I set it up if the distance is greater than 32768 seconds.