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.

 

[David Biddulph]

I am intrigued that you say that I replied incorrectly.

The person who said that you were formatting the cells as [mm.ss] was
yourself (or if not you, the person of the same user name who started the
thread). You'll see it in the quoted material below, but I'll repeat your
words for clarity:
"I first formatted the cells to [mm:ss]"

I'm glad that you got the answer you wanted with [h]:mm:ss as hours minutes
and seconds, but when you originally asked the question you gave the
impression that you wanted the answer in minutes and seconds when it went
beyond an hour, which is why you were given the suggestion to format as
[mm]:ss. In adding your examples (in another branch of this thread) of
48:36 and 45:25, [mm]:ss would show 94:01 for the sum, instead of the
1:34:01 that you will get from [h]:mm:ss. Use whichever you prefer.
--
David Biddulph

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.