Time functions

[Harish]

Hi,

I have to do some time calculations and I have a formula that outputs
in minutes and I have to subtract a standard time to this number of
minutes. For example, I have 8:00 AM in one cell and 20.68 minutes on
an other cell. The output that I am expecting is 7:39 AM. Does anybody
know how to bring an output like that by subtracting the minutes?
Please help. Thanks

 

[Bernard Liengme]

With 8:00 AM in A1 and 20.68 in B1, use this formula
=A1-B1/(24*60)

Why does it work? Because Excel stores time as a fraction of a day. We
convert minutes to days by dividing by 24*60 (24 hr/day and 60 min/hr) You
could just use
=A1-B1/1440

When you use it please do not curse me because it displays 0.318972.
It does this because Excel need to be told to use time format
I general do it this way: click on A1 which has a time forma; click the
Format Painter on the tools bar (in the Home tab in Excel 2007) and click
the cell with the formula.

best wishes

 

[Rick Rothstein]

Assuming your time is in A1 and your minutes are in A2...

=A1-A2/1440

 

[Harish]

Hey Bernard,

Thanks for your solution. I tried the paint format stuff and in return
it gives me ###########, an unrecognized number. Do you know how to
fix that? Thanks

 

[Bernard Liengme]

Excel displays ########### under certain conditions
1) the most common case is when the column is too narrow to display the data
2) another is then the time is negative - check that you A1 and B1 hold the
time and the minutes (not the reverse) and that you really have time in A1.
The formula does work - I tested it before my first reply
best wishes

 

[Harish]

Thanks for your help.

One small question. I used your formula and I am getting like one
minute off. For example, 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 716.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?

 

[JoeU2004]

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 716.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 -----

 

[Bernard Liengme]

It depends what precision you want
Here are the results of your data with time displayed in hours:mins:sec.000
8:00 AM 31.88 7:28:07.200
7:28 AM 11.2 7:16:55.200


But if you want to work to the nearest minute use =A1-ROUND(B1,0)/(24*60)
Here are the results of that
8:00 AM 31.88 7:28:00.000
7:28 AM 11.2 7:17:00.000


Writing time as 716.9) AM is very confusing!

best wishes