Formatting cells

[Enigman]

I have encountered a problem I cannot fix. I have a spreadsheet where I want
to input several cells with minutes and seconds and then total them up in the
bottom cell. ex. I first formatted the cells to [mm:ss]. The problem is
whenever I input minutes and seconds in any one of the cells formatted in
this manner, the cell displays the wrong info. In this example, when I input
[48:55] the cell displays [55:00]. If I input [46:37] it displays [37:00] but
in the formula bar it displays [1/1/1900 10:37:00 PM] without the brackets.
Stranger yet, if I cursor back to the cell and recheck the formatting, Excel
changed the format to [h]:mm:ss and yes, it includes the brackets around the
"h"

This is driving me nuts. Can it be fixed? Please advise.

 

[Luke M]

The problem is how your inputting the data. When using colon, first number
refers to hours. Input your number as
0:48:55

As times and dates are treated similarly in XL, that explains why the
formula bar is displayed that way, but you should still be able to do a
summation.

One final note, are you formatting the cells as
[mm:ss]
or
"["mm:ss"]"

 

[David Biddulph]

[mm.ss] is not a valid format (and Excel should tell you so, so it isn't
clear what you've actually done).
[mm]:ss is what is usually recommended.
You'll also need to make sure that you've got data in minutes and seconds,
not hours and minutes which you're putting in at the moment. Other messages
recently have explained how to deal with that problem.

 

[Enigman]

Thanks for the info Luke. I tried your ideas and still have a problem. Let me
try to explain further.

I am using cells E34 and E35 to input my times in minutes and seconds. I am
using cell E36 as my summation cell. While in E34 I went to Format, Cells,
Custom. In the window there are many choices as you know. I selected the
choice mm:ss. There is no choice with mm:ss in brackets available there. But
using your suggestion, I left my two cells formatted that way and in[ut my
minutes and seconds preceeded by a "0" for no hours. That worked for getting
the correct minutes and seconds into those two data cells. Then for the
summation cell, I input the following formula: =SUM(E34:E35) but the sum of
the two cells does not add up correctly.
In E34 I input 0:48:36 which is correctly displayed as 48:36 and in E35 I
input 0:45:25 which is also displayed correctly as 45:25 but the total that
is displayed in E36 is 34:01.

So your answer was partially correct.

The problem is how your inputting the data. When using colon, first number
refers to hours. Input your number as
0:48:55

As times and dates are treated similarly in XL, that explains why the
formula bar is displayed that way, but you should still be able to do a
summation.

One final note, are you formatting the cells as
[mm:ss]
or
"["mm:ss"]"

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I have encountered a problem I cannot fix. I have a spreadsheet where I want
to input several cells with minutes and seconds and then total them up in the
bottom cell. ex. I first formatted the cells to [mm:ss]. The problem is
whenever I input minutes and seconds in any one of the cells formatted in
this manner, the cell displays the wrong info. In this example, when I input
[48:55] the cell displays [55:00]. If I input [46:37] it displays [37:00] but
in the formula bar it displays [1/1/1900 10:37:00 PM] without the brackets.
Stranger yet, if I cursor back to the cell and recheck the formatting, Excel
changed the format to [h]:mm:ss and yes, it includes the brackets around the
"h"

This is driving me nuts. Can it be fixed? Please advise.

 

[Enigman]

OK Luke, don't bang your head against a wall here. I played around a bit more
and think I have the answer now. I reformatted all three cells to [h]:mm:ss
and using the same times I used in the previous example, I got the correct
summation. I needed the hour option because when Excel added up the minutes
and seconds I input in the data cells they totaled more than an hour.

Thanks again for your help.

The problem is how your inputting the data. When using colon, first number
refers to hours. Input your number as
0:48:55

As times and dates are treated similarly in XL, that explains why the
formula bar is displayed that way, but you should still be able to do a
summation.

One final note, are you formatting the cells as
[mm:ss]
or
"["mm:ss"]"

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I have encountered a problem I cannot fix. I have a spreadsheet where I want
to input several cells with minutes and seconds and then total them up in the
bottom cell. ex. I first formatted the cells to [mm:ss]. The problem is
whenever I input minutes and seconds in any one of the cells formatted in
this manner, the cell displays the wrong info. In this example, when I input
[48:55] the cell displays [55:00]. If I input [46:37] it displays [37:00] but
in the formula bar it displays [1/1/1900 10:37:00 PM] without the brackets.
Stranger yet, if I cursor back to the cell and recheck the formatting, Excel
changed the format to [h]:mm:ss and yes, it includes the brackets around the
"h"

This is driving me nuts. Can it be fixed? Please advise.

 

[Enigman]

Hey David. Thanks for the suggestions. But you replied incorrectly. You said
I was formatting the cells as [mm.ss] but that is wrong. I was formatting the
cells as mm:ss. But I played around with this again based on your ideas and
Luke M.'s too and found the solution.

I reformatted all three cells using [h]:mm:ss and I input my minutes and
seconds using a "0" in front of my minutes for the hours. These two changes
solved my problem.

Thanks for the help.

[mm.ss] is not a valid format (and Excel should tell you so, so it isn't
clear what you've actually done).
[mm]:ss is what is usually recommended.
You'll also need to make sure that you've got data in minutes and seconds,
not hours and minutes which you're putting in at the moment. Other messages
recently have explained how to deal with that problem.
--
David Biddulph

I have encountered a problem I cannot fix. I have a spreadsheet where I
want
to input several cells with minutes and seconds and then total them up in
the
bottom cell. ex. I first formatted the cells to [mm:ss]. The problem is
whenever I input minutes and seconds in any one of the cells formatted in
this manner, the cell displays the wrong info. In this example, when I
input
[48:55] the cell displays [55:00]. If I input [46:37] it displays [37:00]
but
in the formula bar it displays [1/1/1900 10:37:00 PM] without the
brackets.
Stranger yet, if I cursor back to the cell and recheck the formatting,
Excel
changed the format to [h]:mm:ss and yes, it includes the brackets around
the
"h"

This is driving me nuts. Can it be fixed? Please advise.