Prev: Selecting specific cells in a column using countif
Next: Ignore Blanks
From: Chip Pearson on 27 Apr 2010 18:41
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]




On Tue, 27 Apr 2010 11:19:01 -0700, da <da(a)discussions.microsoft.com>
wrote:

>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
First  |  Prev  | 
Pages: 1 2
Prev: Selecting specific cells in a column using countif
Next: Ignore Blanks