Cumulative time calculation in Excel 2003

[Colin Hayes]

Hi all

I'm having trouble making my worksheet calculate minutes elapsed.

Presently I have this in A and B , where A is adding cumulatively the
times showing in B :

A B

Mins/Secs Mins/Secs

Total

0.00 2.00
2.00 2.30
4.30 3.00
7.30 3.30
10.60 4.00
14.60 4.30
18.90 5.00
23.90 5.30

Clearly , column A should be round to minutes and seconds and not base
10.

The chart should read like this :

A B

Mins/Secs Mins/Secs

Total

0.00 2.00
2.00 2.30
4.30 3.00
7.30 3.30
11.00 4.00
15.00 4.30
19.30 5.00
24.30 5.30

In column A presently I'm using

=A4+B4

in the first calculation cell and dragging down to make the calculation.

Can someone suggest an alternative formula to have it work out
accumulated minutes and seconds correctly , please?

thanks.

 

[joeu2004]

I'm having trouble making my worksheet calculate minutes elapsed. [....]
A B
Mins/Secs Mins/Secs Total
0.00 2.00
2.00 2.30
4.30 3.00
7.30 3.30
10.60 4.00
14.60 4.30
18.90 5.00
23.90 5.30 [....]
Clearly , column A should be round to minutes and seconds
and not base 10. [....]
In column A presently I'm using =A4+B4
in the first calculation cell and dragging down to make the
calculation.

The first problem is: mins/secs should be represented as m:ss, not m.ss --
a colon instead of period.

I don't know of anywhere that period is used instead of colon. (Please
enlighten me.)

If you are amenable to changing the representation, your formula will work
as-is -- almost. See further comments below.

Otherwise, change you formula as follows:

=--TEXT(TEXT(A4*100,"\0\:0\:00")+TEXT(B4*100,"\0\:0\:00"),"[m].ss")

This will work for total time up to 9999.59 (as you write it).

-----

If you change the representation to m:ss, you should input numbers in column
B in the form 0:m:ss or m:ss.0. If you enter just m:ss, Excel will
interpret that as h:mm.

The format of column A and perhaps column B should be Custom [m]:ss. That
will display minutes greater than 59.

Finally, it would be prudent to change your formula as follows:

=--TEXT(A4+B4,"h:mm:ss")

That is the equivalent of rounding to the second. It eliminates arithmetic
anomalies that arise because of the way that Excel represents number
(including time), called 64-bit binary floating-point. This ensures
equality if you compared the cumulative sum to an equivalent constant.

 

[Colin Hayes]

The first problem is: mins/secs should be represented as m:ss, not m.ss -- a
colon instead of
period.

I don't know of anywhere that period is used instead of colon. (Please enlighten
me.)

If you are amenable to changing the representation, your formula will work as-is --
almost. See
further comments below.

Otherwise, change you formula as follows:

=--TEXT(TEXT(A4*100,"\0\:0\:00")+TEXT(B4*100,"\0\:0\:00"),"[m].ss")

This will work for total time up to 9999.59 (as you write it).

-----

If you change the representation to m:ss, you should input numbers in column B in
the form 0:m:ss
or m:ss.0. If you enter just m:ss, Excel will interpret that as h:mm.

The format of column A and perhaps column B should be Custom [m]:ss. That will
display minutes
greater than 59.

Finally, it would be prudent to change your formula as follows:

=--TEXT(A4+B4,"h:mm:ss")

That is the equivalent of rounding to the second. It eliminates arithmetic anomalies
that arise
because of the way that Excel represents number (including time), called 64-bit
binary floating-
point. This ensures equality if you compared the cumulative sum to an equivalent
constant.

Hi

Many thanks for your help with this. It's working fine now.

I tried various things to get this to work , and am grateful (relieved!)
that your expertise has solved it.

I used your first formula , and it worked perfectly first time.


Best Wishes