adding hours and minutes

[da]

Hello
I am not getting total sum when trying to add following times. I have used
H;mm format. I get total of 9:20, which is wrong. How can I get accurate
total?
Thanks
1:50
0:45
0:40
2:40
1:40
0:20
0:30
0:00
1:30
0:25
2:40
2:30
2:30
2:40
0:50
1:05
2:15
4:00
1:20
2:40
0:50
0:25
1:10
2:00
0:50
2:25
2:15
1:50
0:50
1:40
2:50
2:45
2:30
2:30
2:35
2:10
4:30
0:55
2:30
2:40
2:45
2:30

 

[FSt1]

hi
try format [hh]:mm:ss

Regards
FSt1

 

[da]

Thanks
I changed the format as suggested. But, I still get total 9 hours and 20
minutes, which is worng.

hi
try format [hh]:mm:ss

Regards
FSt1

Hello
I am not getting total sum when trying to add following times. I have used
H;mm format. I get total of 9:20, which is wrong. How can I get accurate
total?
Thanks
1:50
0:45
0:40
2:40
1:40
0:20
0:30
0:00
1:30
0:25
2:40
2:30
2:30
2:40
0:50
1:05
2:15
4:00
1:20
2:40
0:50
0:25
1:10
2:00
0:50
2:25
2:15
1:50
0:50
1:40
2:50
2:45
2:30
2:30
2:35
2:10
4:30
0:55
2:30
2:40
2:45
2:30

 

[Bob I]

Use HH:mm

 

[Luke M]

With a format of:
[h]:mm
I get an answer of 78:15

Also, even with a regular format of:
h:mm
I get an answer of 6:15

--
Best Regards,

Luke M

Thanks
I changed the format as suggested. But, I still get total 9 hours and 20
minutes, which is worng.

hi
try format [hh]:mm:ss

Regards
FSt1

Hello
I am not getting total sum when trying to add following times. I have
used
H;mm format. I get total of 9:20, which is wrong. How can I get
accurate
total?
Thanks
1:50
0:45
0:40
2:40
1:40
0:20
0:30
0:00
1:30
0:25
2:40
2:30
2:30
2:40
0:50
1:05
2:15
4:00
1:20
2:40
0:50
0:25
1:10
2:00
0:50
2:25
2:15
1:50
0:50
1:40
2:50
2:45
2:30
2:30
2:35
2:10
4:30
0:55
2:30
2:40
2:45
2:30

 

[Joe User]

I changed the format as suggested. But, I still get
total 9 hours and 20 minutes, which is worng.

First, it might not have been what format should be changed. Change the
format of the cell where you compute the sum to [h]:mm.

Second, it is not clear how you computing the sum. I ass-u-me you are using
=SUM(A1:A42).

When copy-and-paste the column of posted numbers and compute SUM in a cell
formatted as h:mm, the result is displayed misleadingly 6:15, not 9:20. It
displays correctly as 78:15 with the format [h]:mm.

So I wonder if some of your times are entered as text instead of numbers.
SUM will ignore the text. I suggest you enter the formula in the form
=ISNUMBER(A1) in all cells in a parallel column. Be sure that the results
are all TRUE.

An alternative explanation is that you simply have some typos in the data
that you posted.


----- original message -----

Thanks
I changed the format as suggested. But, I still get total 9 hours and 20
minutes, which is worng.

hi
try format [hh]:mm:ss

Regards
FSt1

Hello
I am not getting total sum when trying to add following times. I have used
H;mm format. I get total of 9:20, which is wrong. How can I get accurate
total?
Thanks
1:50
0:45
0:40
2:40
1:40
0:20
0:30
0:00
1:30
0:25
2:40
2:30
2:30
2:40
0:50
1:05
2:15
4:00
1:20
2:40
0:50
0:25
1:10
2:00
0:50
2:25
2:15
1:50
0:50
1:40
2:50
2:45
2:30
2:30
2:35
2:10
4:30
0:55
2:30
2:40
2:45
2:30

 

[da]

Thank you to all who responded. The formula did work after changing the format.

I changed the format as suggested. But, I still get
total 9 hours and 20 minutes, which is worng.

First, it might not have been what format should be changed. Change the
format of the cell where you compute the sum to [h]:mm.

Second, it is not clear how you computing the sum. I ass-u-me you are using
=SUM(A1:A42).

When copy-and-paste the column of posted numbers and compute SUM in a cell
formatted as h:mm, the result is displayed misleadingly 6:15, not 9:20. It
displays correctly as 78:15 with the format [h]:mm.

So I wonder if some of your times are entered as text instead of numbers.
SUM will ignore the text. I suggest you enter the formula in the form
=ISNUMBER(A1) in all cells in a parallel column. Be sure that the results
are all TRUE.

An alternative explanation is that you simply have some typos in the data
that you posted.


----- original message -----

Thanks
I changed the format as suggested. But, I still get total 9 hours and 20
minutes, which is worng.

hi
try format [hh]:mm:ss

Regards
FSt1

:

Hello
I am not getting total sum when trying to add following times. I have used
H;mm format. I get total of 9:20, which is wrong. How can I get accurate
total?
Thanks
1:50
0:45
0:40
2:40
1:40
0:20
0:30
0:00
1:30
0:25
2:40
2:30
2:30
2:40
0:50
1:05
2:15
4:00
1:20
2:40
0:50
0:25
1:10
2:00
0:50
2:25
2:15
1:50
0:50
1:40
2:50
2:45
2:30
2:30
2:35
2:10
4:30
0:55
2:30
2:40
2:45
2:30

 

[Chip Pearson]

Times are just numbers (a fraction of a 24 hour day; 0.25 = 6:00, 0.5
= 12:00, 0.75 = 18:00 etc), so you can simply add up the values.
However, when Excel displays a time, it does so in "clock" format,
"rolling over" at 24:00:00. Thus, a time of 36:00:00 is displayed in
the cell as 12:00:00. You can use the [hh] format specifier to
prevent this roll over. E.g.,

=SUM(A1:A100)

and then go to the Format menu, choose Cells, then the Number tab.
Choose Custom from the category list and enter

[hh]:mm:ss

Do your times indicate hours:minutes or minutes:seconds. If they
indicate hours:minutes, the just use SUM and format the result as
[hh]:mm. If your values are supposed to indicate minutes:seconds,
you'll need to divide the sum by 60 to get the correct value.

=SUM(A1:A100)/60

and use a custom number format of [mm]:ss. The [mm] formatter tells
Excel not to roll over displayed minutes at 60 minutes, just as the
[hh] shown above prevents a rollover at 24 hours. E.g., 90 mintues 15
seconds is displayed as 90:15 rather than 30:15.

Note that the formatting applied to a cell in no way modifies the
actual value of the cell, and does not alter how you must enter dates
and/or times. That is, there is no way using formatting alone to allow
an input of 1234 to mean 12:34:00.


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]