Harish said:
if I have 8:00 AM and I subtract 31.88 minutes from
it and after that I again subtract 11.2 minutes. I
should get 7
16.9) AM and the result should yield
me 7:17 AM. But in the formula that you gave me, I
am getting 7:16 AM instead of 7:17 AM. Do you know
how to fix this?
The actual value does appear to be about 7:16.92, as it should be. You can
verify that with the following formula. If A5 has the "exact" time, the
following extracts the number of minutes:
=A5*1440 - INT(A5*24)*60
So the problem is that the h:mm format truncates to a minute instead of
rounding :-(.
You could round the value yourself, in one of two ways. But note that these
will change the actual value; that is, you will lose precision. So you
might want to retain the time value in two cells: one that has the "exact"
time; the other that rounds to a minute.
If A5 has the "exact" time as you computed using Bernard's formula, the time
rounded to a minute is:
=ROUND(A5*1440,0)/1440
Alternatively, you could compute the rounded time directly. If A3 has the
"exact" result of 8:00 minus 31.88 minutes and A4 has the next change
(11.2), the time rounded to a minute is:
=ROUND(A3*1440-A4,0)/1440
Bear in mind that since time in that format is stored as a fraction of a
day, the computation is subject to numerical abberations because most
decimal fractions cannot be stored exactly. If you perform enough
individual additions and/or subtractions, you might begin to see unexpected
results.
So I wonder if you want to use a modified form of the last formula anyway,
perhaps rounding to 2 decimal places instead of zero for the "exact" time
value.
----- original message -----
