TIME function capped at ~9 hours?

[indesignkat]

I have a report in which whole number seconds are converted to a time format. I used to do this by referencing the number of seconds and dividing by 86,400, but then I found the TIME function and started using it instead.


A1 = 4000


=time(,,A1) will return 0:00:04, if you have the cell formatted for h:mm:ss. Pretty handy. But I notice that when the number of seconds gets over 32,767 the time formula returns a number error. That amounts to 9 hours, 6 minutes, and 7 seconds.


Any ideas why?

 

[jeffreyweir]

I think the answer here is 'because'.

http://office.microsoft.com/en-nz/excel-help/time-function-HP010062295.aspx

 

[Hui]

Indesignkat


Because is the answer I give my kids


The problem occurs because internally Excel expects the components of the Time Function to be delivered as Integers.

Integers have a maximum value of 32767 which is 2^15-1

Even though Excel now has a 64 bit version a lot of these older legacy issues still remain


The best way to use time is to use it properly and specify the time as hh:mm:ss


hh:mm:ss is Hrs, Mins, Seconds

hh:mm is Hrs, Mins

hh: is Hrs

0:0:ss is seconds

0:mm:ss is Mins, Seconds etc

 

[jeffreyweir]

Ahhh. I thought that number rang a bell. That will go some way to explaining why excel 2003 only has 32767 * 2 rows, give or take a few rows.


Thanks Hui.

 

[Hui]

It is 2^15-1 as the first value is 0

and hence the 2^15th value is 1 less than 2^15


You see the numbers

2^8 = 256

2^15 = 32768

2^16 = 65536

2^32 = 4294967296

or 1 less, pop up all the time as limits

These indicate the underlying data structures


eg: Excel up to 2003 only supported 256 Columns

 

[indesignkat]

Hui,


Thank you for the detailed explanation, but I'm unclear what you meant by "The best way to use time is to use it properly and specify the time as hh:mm:ss". By 'use it properly', you mean only use it on numbers below the integer threshold?


In your opinion, is the 'divide by 86400' method the proper way to format numbers of seconds over 32767 to hh:mm:ss format?

 

[SirJB7]

Hi, indesignkat!

I think that Hui tried to mean to prepare input parameters for time cells to be within the range of expected values, i.e., don't use directly any huge number of seconds if you suspect that they might be greater than integer values (>32767) and instead of that use helper columns to reduce that number of seconds to minutes (dividing by 60 and taking the integer part, where the rest are the actual seconds), and proceeding similarly for minutes, and hours. Then use TIME function.

Regards!