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!
Bookmarks