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.

 

[Spencer101]

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 th
calculation.

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

thanks.

Hi,

I think this is more about data entry and formatting than formulas.

There's nothing wrong with the formula you're using, but try enterin
the data as 00:00:00 format (with :'s rather than .'s).

You can format the data to "mm:ss" so as not to show the hours if you s
desire.

Then you need to decide what you want to data to do if/when if loop
past an hour. Will it go back to zero and continue, or just continu
past 60 minutes and carry on meaning it could show 72 minutes rathe
than 1 hour 12 minutes...

Simple to do either way, just depends on what you need.

S

 

[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