Minute Bug

  • Thread starter Thread starter O Grossman
  • Start date Start date
O

O Grossman

I think I have found a bug in the "minute()" function. If
you perform the function on a time that has seconds
greater than 59.50 it returns a minute value that is one
minute greater than the proper value. The tricky part is
that you won't see this bug unless you apply a custom
format to the cell to display the fractional seconds. If
you display your time value with a format that only shows
integer seconds then the time value always appears to
agree with the minute() function.

Does anyone know how to find out if microsoft is aware of
this bug(or how to report the bug)?

Thanks,
Owen
 
I think I have found a bug in the "minute()" function. If
you perform the function on a time that has seconds
greater than 59.50 it returns a minute value that is one
minute greater than the proper value. The tricky part is
that you won't see this bug unless you apply a custom
format to the cell to display the fractional seconds. If
you display your time value with a format that only shows
integer seconds then the time value always appears to
agree with the minute() function.
Click to expand...

I doubt anyone at Microsoft would consider this is a bug. No one should try
doing anything that relies on time granularity finer than 1 second in a
spreadsheet. Spreadsheets aren't real time processing tools.
 
There is no time function for accessing fractions of a second, therefore
HOURS() MINUTES() and SECONDS() return their values based on time
rounded to the nearest second.

However, you can roll your own. Time is stored as a decimal fraction of
a day. Therefore 02:15:59.6 would be stored as
2/24+15/24/60+59.6/24/60/60 = 0.0944398148148148
as you will see if you reformat the time to General. Thus
=60*(A6*24-INT(A6*24))
will return 15.9933333333333 minutes, when formatted to General.
=60*(A6*24*60-INT(A6*24*60))
will return 59.6000000000004 seconds, when formatted to General.

The 0.0000000000004 discrepancy in the seconds calculation is not a bug
either. Most decimal fractions cannot be exactly represented in binary,
and 0.0000000000004 merely reveals the amount of approximation that was
required to represent 02:15:59.6. The approximate representation of
time may be either slightly larger or slightly smaller than than the
intended value, so for values extremely near a minute boundary, you may
get unexpected results if you don't round to some degree.

Another potential issue would be the source of your time values where
you are concerned about fractions of seconds. If you are timing
calculations by subtracting the end time from the start time, you will
find it difficult to get sufficient resolution. See

http://groups.google.com/groups?threadm=3E03104E.3030001@no_e-mail.com

for details.

Jerrry
 
Back
Top