adding minutes and seconds correctly

[Guest]

Hi, I have a spreadsheet which holds data as minutes and seconds i.e. 13:57 =
13 minutes and 57 seconds. When I try to add up the column, Excel thinks 13
represents hours and 57 is minutes and adds up accordingly. I've tried using
custom to make the field MM:SS without any success. What am I doing wrong or
does someone know how to resolve this? Many thanks in advance for your help.

Cheers

T

 

[Pete_UK]

This often happens if you have imported the data. All you need to do is
divide your total by 60, i.e. something like:

=SUM(A1:A50)/60

An alternative approach is to reset all the values to minutes and
seconds. Enter 60 in a blank cell, select that cell and click <copy>.
Then highlight the column with the erroneous times in and Edit | Paste
Special | Divide (check) then OK and <Esc>. You will now need to format
your cells as mm:ss (or [m]:ss to prevent roll-up).

Your sum formula should work correctly with this approach.

Hope this helps.

Pete

 

[Ron Rosenfeld]

Hi, I have a spreadsheet which holds data as minutes and seconds i.e. 13:57 =
13 minutes and 57 seconds. When I try to add up the column, Excel thinks 13
represents hours and 57 is minutes and adds up accordingly. I've tried using
custom to make the field MM:SS without any success. What am I doing wrong or
does someone know how to resolve this? Many thanks in advance for your help.

Cheers

T

To enter the data so that Excel will interpret it as mm:ss, you must enter it
either as:

0:13:57 or
13:57.0

Otherwise it will interpret it as hh:mm

To convert the data that has already been entered as hh:mm to mm:ss, merely
divide by 60.

For example:

=SUMPRODUCT(A1:A100/60)


--ron

 

[Guest]

Hi Ron

Many thanks for your speedy response however, its not that easy I don't
think! The data is shown as 13:57 and I realise that it is the absence of the
leading 00: which is causing my problems. However, I can't find an easy way
to insert the 00:. As the data is already input as MM:SS, but being displayed
as HH:MM I can't use your formula because it will give me different figures.
I hope this makes sense!

So I either need a snazzy way to add 00: in front of my existing data or to
add .0 behind. Any ideas?

Thanks

T

 

[Guest]

Woohoo!!! Pete you're a star!!! It worked perfectly - I used the second
choice i.e. typing 60 in a column etc.

Many thanks!

This often happens if you have imported the data. All you need to do is
divide your total by 60, i.e. something like:

=SUM(A1:A50)/60

An alternative approach is to reset all the values to minutes and
seconds. Enter 60 in a blank cell, select that cell and click <copy>.
Then highlight the column with the erroneous times in and Edit | Paste
Special | Divide (check) then OK and <Esc>. You will now need to format
your cells as mm:ss (or [m]:ss to prevent roll-up).

Your sum formula should work correctly with this approach.

Hope this helps.

Pete

Hi, I have a spreadsheet which holds data as minutes and seconds i.e. 13:57 =
13 minutes and 57 seconds. When I try to add up the column, Excel thinks 13
represents hours and 57 is minutes and adds up accordingly. I've tried using
custom to make the field MM:SS without any success. What am I doing wrong or
does someone know how to resolve this? Many thanks in advance for your help.

Cheers

T

 

[Pete_UK]

You're welcome. I thought you were ignoring my post at first!

Pete

Woohoo!!! Pete you're a star!!! It worked perfectly - I used the second
choice i.e. typing 60 in a column etc.

Many thanks!

This often happens if you have imported the data. All you need to do is
divide your total by 60, i.e. something like:

=SUM(A1:A50)/60

An alternative approach is to reset all the values to minutes and
seconds. Enter 60 in a blank cell, select that cell and click <copy>.
Then highlight the column with the erroneous times in and Edit | Paste
Special | Divide (check) then OK and <Esc>. You will now need to format
your cells as mm:ss (or [m]:ss to prevent roll-up).

Your sum formula should work correctly with this approach.

Hope this helps.

Pete

Hi, I have a spreadsheet which holds data as minutes and seconds i.e. 13:57 =
13 minutes and 57 seconds. When I try to add up the column, Excel thinks 13
represents hours and 57 is minutes and adds up accordingly. I've tried using
custom to make the field MM:SS without any success. What am I doing wrong or
does someone know how to resolve this? Many thanks in advance for your help.

Cheers

T

 

[Guest]

Nope, I got a notification of a reply (from Ron) and must have been answering
his post when your's arrived ;o)

Once again, many thanks from a mathematical numpty!

T

You're welcome. I thought you were ignoring my post at first!

Pete

Woohoo!!! Pete you're a star!!! It worked perfectly - I used the second
choice i.e. typing 60 in a column etc.

Many thanks!

This often happens if you have imported the data. All you need to do is
divide your total by 60, i.e. something like:

=SUM(A1:A50)/60

An alternative approach is to reset all the values to minutes and
seconds. Enter 60 in a blank cell, select that cell and click <copy>.
Then highlight the column with the erroneous times in and Edit | Paste
Special | Divide (check) then OK and <Esc>. You will now need to format
your cells as mm:ss (or [m]:ss to prevent roll-up).

Your sum formula should work correctly with this approach.

Hope this helps.

Pete

Toosh wrote:
Hi, I have a spreadsheet which holds data as minutes and seconds i.e. 13:57 =
13 minutes and 57 seconds. When I try to add up the column, Excel thinks 13
represents hours and 57 is minutes and adds up accordingly. I've tried using
custom to make the field MM:SS without any success. What am I doing wrong or
does someone know how to resolve this? Many thanks in advance for your help.

Cheers

T

 

[Ron Rosenfeld]

Hi Ron

Many thanks for your speedy response however, its not that easy I don't
think! The data is shown as 13:57 and I realise that it is the absence of the
leading 00: which is causing my problems. However, I can't find an easy way
to insert the 00:. As the data is already input as MM:SS, but being displayed
as HH:MM I can't use your formula because it will give me different figures.
I hope this makes sense!

So I either need a snazzy way to add 00: in front of my existing data or to
add .0 behind. Any ideas?

Thanks

T

You misunderstood what I wrote.

I did not suggest changing what you have already entered. I merely tried to
show you what Excel was doing.

Also, I guess you never tried the formula.


--ron

 

[Guest]

Hi Ron

Yes, I think I did misunderstand what you wrote because reading it back, I
think it was similar to what Pete wrote. I just understood the way he
explained it better. But many thanks for your help anyway )

Cheers

T