I have read a bunch of the previous questions, none seem to finish what I need.

I have 4300 cells, in a single column,all with times formatted as shown:
[hh]:mm, or represented as: 03:12; 3-hours, 12-minutes. I tried to add the
times as follows: sum(m2:m4300) which keeps adding into 0. Does anyone have
a way to add these in the sum block without having to do this:
sum(m2+m3+m4+m5) all the way to m4300. Any thoughts? Thanks in advance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

If you add up a lot of times, they will quickly exceed 24 hours, and
Excel will start to show them as days (or dates). Just apply a custom
format to the cell with the formula in, set to [hh]:mm - this will
prevent hours above 24 being displayed as days. However, there is a
maximum number of hours that can be shown with this format - I think
it is 9999, so if you have 4000+ cells and your times are typically 3
hours or more, then you will exceed this limit.

Hope this helps.

Pete

On Apr 19, 4:18 pm, Champ wrote:
I have read a bunch of the previous questions, none seem to finish what I need.

I have 4300 cells, in a single column,all with times formatted as shown:
[hh]:mm, or represented as: 03:12; 3-hours, 12-minutes. I tried to add the
times as follows: sum(m2:m4300) which keeps adding into 0. Does anyone have
a way to add these in the sum block without having to do this:
sum(m2+m3+m4+m5) all the way to m4300. Any thoughts? Thanks in advance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm....mspx?mid=4d45...

Are you certain you have those cells formatted as TIME and not as text? I
just set up 3 cells formatted as Time using the 13:30 format shown in the
format picker as:
3:12
6:18
9:30
and then below them put
=Sum(A1:A3) and got the expected 19:00 result. But in 3 other cells
formatted as Text, the result was also my expected 0 (zero).


"Champ" wrote:

I have read a bunch of the previous questions, none seem to finish what I need.

I have 4300 cells, in a single column,all with times formatted as shown:
[hh]:mm, or represented as: 03:12; 3-hours, 12-minutes. I tried to add the
times as follows: sum(m2:m4300) which keeps adding into 0. Does anyone have
a way to add these in the sum block without having to do this:
sum(m2+m3+m4+m5) all the way to m4300. Any thoughts? Thanks in advance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Yes they are as times. I can add them by doing this:
=sum(m2+m3+m4+m5........) and they add just fine, but not any other way, like
this: =sum(m2:m4300) it still comes as 0 zero. I am trying to add very small
segemnts of time; i.e. 00:05, 00:12, 00:03, etc. no joy.... Any other
thoughts? Thanks so far...

"JLatham" wrote:

Are you certain you have those cells formatted as TIME and not as text? I
just set up 3 cells formatted as Time using the 13:30 format shown in the
format picker as:
3:12
6:18
9:30
and then below them put
=Sum(A1:A3) and got the expected 19:00 result. But in 3 other cells
formatted as Text, the result was also my expected 0 (zero).


"Champ" wrote:

I have read a bunch of the previous questions, none seem to finish what I need.

I have 4300 cells, in a single column,all with times formatted as shown:
[hh]:mm, or represented as: 03:12; 3-hours, 12-minutes. I tried to add the
times as follows: sum(m2:m4300) which keeps adding into 0. Does anyone have
a way to add these in the sum block without having to do this:
sum(m2+m3+m4+m5) all the way to m4300. Any thoughts? Thanks in advance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

It sounds like the stuff in M2:M4300 isn't really times--just text that looks
like times.

I'd try this first:
Select M2:m4300
Edit|Replace
what: : (colon)
with: :
replace all

Since you're changing the cells, excel will see them as times.

Champ wrote:

Yes they are as times. I can add them by doing this:
=sum(m2+m3+m4+m5........) and they add just fine, but not any other way, like
this: =sum(m2:m4300) it still comes as 0 zero. I am trying to add very small
segemnts of time; i.e. 00:05, 00:12, 00:03, etc. no joy.... Any other
thoughts? Thanks so far...

"JLatham" wrote:

Are you certain you have those cells formatted as TIME and not as text? I
just set up 3 cells formatted as Time using the 13:30 format shown in the
format picker as:
3:12
6:18
9:30
and then below them put
=Sum(A1:A3) and got the expected 19:00 result. But in 3 other cells
formatted as Text, the result was also my expected 0 (zero).


"Champ" wrote:

I have read a bunch of the previous questions, none seem to finish what I need.

I have 4300 cells, in a single column,all with times formatted as shown:
[hh]:mm, or represented as: 03:12; 3-hours, 12-minutes. I tried to add the
times as follows: sum(m2:m4300) which keeps adding into 0. Does anyone have
a way to add these in the sum block without having to do this:
sum(m2+m3+m4+m5) all the way to m4300. Any thoughts? Thanks in advance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

--

Dave Peterson

Sorry about the delay in resonding, impromptu meeting. I did the replace all
: function and low and behold, the time came as it should have. That was the
trick. You folks have all been outstanding through every need I have ever had
here. Thank you all so much for your time and efforts. Champ.

"Dave Peterson" wrote:

It sounds like the stuff in M2:M4300 isn't really times--just text that looks
like times.

I'd try this first:
Select M2:m4300
Edit|Replace
what: : (colon)
with: :
replace all

Since you're changing the cells, excel will see them as times.

Champ wrote:

Yes they are as times. I can add them by doing this:
=sum(m2+m3+m4+m5........) and they add just fine, but not any other way, like
this: =sum(m2:m4300) it still comes as 0 zero. I am trying to add very small
segemnts of time; i.e. 00:05, 00:12, 00:03, etc. no joy.... Any other
thoughts? Thanks so far...

"JLatham" wrote:

Are you certain you have those cells formatted as TIME and not as text? I
just set up 3 cells formatted as Time using the 13:30 format shown in the
format picker as:
3:12
6:18
9:30
and then below them put
=Sum(A1:A3) and got the expected 19:00 result. But in 3 other cells
formatted as Text, the result was also my expected 0 (zero).


"Champ" wrote:

I have read a bunch of the previous questions, none seem to finish what I need.

I have 4300 cells, in a single column,all with times formatted as shown:
[hh]:mm, or represented as: 03:12; 3-hours, 12-minutes. I tried to add the
times as follows: sum(m2:m4300) which keeps adding into 0. Does anyone have
a way to add these in the sum block without having to do this:
sum(m2+m3+m4+m5) all the way to m4300. Any thoughts? Thanks in advance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

--

Dave Peterson

If they really are Time entries (which you seem to have confirmed), then see
what Pete_UK had to say about limit in the first response to your original
posting - he may have the answer the that the total amount of time is
blowing you out of the water. 4000 * 3 minutes = 12000 minutes = 200 days
and that's well beyond the limits he mentioned.

"Champ" wrote:

Yes they are as times. I can add them by doing this:
=sum(m2+m3+m4+m5........) and they add just fine, but not any other way, like
this: =sum(m2:m4300) it still comes as 0 zero. I am trying to add very small
segemnts of time; i.e. 00:05, 00:12, 00:03, etc. no joy.... Any other
thoughts? Thanks so far...

"JLatham" wrote:

Are you certain you have those cells formatted as TIME and not as text? I
just set up 3 cells formatted as Time using the 13:30 format shown in the
format picker as:
3:12
6:18
9:30
and then below them put
=Sum(A1:A3) and got the expected 19:00 result. But in 3 other cells
formatted as Text, the result was also my expected 0 (zero).


"Champ" wrote:

I have read a bunch of the previous questions, none seem to finish what I need.

I have 4300 cells, in a single column,all with times formatted as shown:
[hh]:mm, or represented as: 03:12; 3-hours, 12-minutes. I tried to add the
times as follows: sum(m2:m4300) which keeps adding into 0. Does anyone have
a way to add these in the sum block without having to do this:
sum(m2+m3+m4+m5) all the way to m4300. Any thoughts? Thanks in advance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc