HELP--- adding duration of times

[Guest]

I have entered in duration of times (hh:mm:ss). When I tried using the "SUM"
function. The answer wasn't correct.

Do I need to format the cells a particular way for the answer to be correct?

I do NOT want the answer to come out as a decimal.

 

[Piscator]

You didn't say what was appearing but yes, you probably need to format
the total cell.
Format, Cell, Number, Time and select the most appropriate. Mine has
a 37:30:55 option which should be OK for you.

 

[Guest]

Thanx for the quick response. However, I tried using that format and it is
not adding up.

Here's what is happening:

1:03:06
:42:39
1:07:10
:55:54
:09:49
1:17:18
The sum is 3:27:34

 

[Peo Sjoblom]

It's correct, it is whomever entered times as :mm:ss (leading colon) that
made a mistake. Excel interprets this as text, so change the :42:39 to
0:42:39 and all the other entries that were made like that the same way and
you should get what you expect

 

[Guest]

That doesn't work for me. I'm using the help file's SUM(A2:A6)*24 because
it's more than 24 hours. I'm adding:

4:05
7:00
1:20
6:45
4:30
1:05
1:55
5:15
0:15
1:45
3:20
0:35

and I'm getting 20:00 as the result, which is obviously wrong. Why does the
summation not work?

 

[David Biddulph]

If you are trying to display hours and minutes beyond 24 hours, you need to
format the cells as [h]:mm, not as h:mm, and thus the 12 numbers you quoted
will sum to 37:50.
In multiplying by 24 you get 37.833333, which is the value in number of
hours (not in Excel time format), which you should be formatting as General
or Number. You seem to have multiplied by 24 but still tried to display as
if it were an Excel time.
37.833333 is equivalent to 37 days and 20 hours, so in displaying as a time
you are seeing the 20 hours as 20:00.

 

[Guest]

I reformatted the cell to be [h]:mm, now it displays 926:00

Any other suggestions?

If you are trying to display hours and minutes beyond 24 hours, you need to
format the cells as [h]:mm, not as h:mm, and thus the 12 numbers you quoted
will sum to 37:50.
In multiplying by 24 you get 37.833333, which is the value in number of
hours (not in Excel time format), which you should be formatting as General
or Number. You seem to have multiplied by 24 but still tried to display as
if it were an Excel time.
37.833333 is equivalent to 37 days and 20 hours, so in displaying as a time
you are seeing the 20 hours as 20:00.
--
David Biddulph

That doesn't work for me. I'm using the help file's SUM(A2:A6)*24 because
it's more than 24 hours. I'm adding:

4:05
7:00
1:20
6:45
4:30
1:05
1:55
5:15
0:15
1:45
3:20
0:35

and I'm getting 20:00 as the result, which is obviously wrong. Why does
the
summation not work?

 

[David Biddulph]

I would expect it to read 908:00, not 926:00, but *please* read my message
again.

EITHER multiply by 24 and format as General or Number to display decimal
hours
OR *don't* multiply by 24, and format as [h]:mm to display as hours and
minutes.
--
David Biddulph

I reformatted the cell to be [h]:mm, now it displays 926:00

Any other suggestions?

If you are trying to display hours and minutes beyond 24 hours, you need
to
format the cells as [h]:mm, not as h:mm, and thus the 12 numbers you
quoted
will sum to 37:50.
In multiplying by 24 you get 37.833333, which is the value in number of
hours (not in Excel time format), which you should be formatting as
General
or Number. You seem to have multiplied by 24 but still tried to display
as
if it were an Excel time.
37.833333 is equivalent to 37 days and 20 hours, so in displaying as a
time
you are seeing the 20 hours as 20:00.
--
David Biddulph

That doesn't work for me. I'm using the help file's SUM(A2:A6)*24
because
it's more than 24 hours. I'm adding:

4:05
7:00
1:20
6:45
4:30
1:05
1:55
5:15
0:15
1:45
3:20
0:35

and I'm getting 20:00 as the result, which is obviously wrong. Why does
the
summation not work?

:

It's correct, it is whomever entered times as :mm:ss (leading colon)
that
made a mistake. Excel interprets this as text, so change the :42:39 to
0:42:39 and all the other entries that were made like that the same
way
and
you should get what you expect


--
Regards,

Peo Sjoblom



Thanx for the quick response. However, I tried using that format and
it
is
not adding up.

Here's what is happening:

1:03:06
:42:39
1:07:10
:55:54
:09:49
1:17:18
The sum is 3:27:34
--
which is incorrect it should be 5:15:56.

Please respond...
Barbara W


:

You didn't say what was appearing but yes, you probably need to
format
the total cell.
Format, Cell, Number, Time and select the most appropriate. Mine
has
a 37:30:55 option which should be OK for you.

 

[Guest]

I did reread it, and it still implies I should try what I did. However, your
last post was much clearer and following that it worked.

thanx!
david

I would expect it to read 908:00, not 926:00, but *please* read my message
again.

EITHER multiply by 24 and format as General or Number to display decimal
hours
OR *don't* multiply by 24, and format as [h]:mm to display as hours and
minutes.
--
David Biddulph

I reformatted the cell to be [h]:mm, now it displays 926:00

Any other suggestions?

If you are trying to display hours and minutes beyond 24 hours, you need
to
format the cells as [h]:mm, not as h:mm, and thus the 12 numbers you
quoted
will sum to 37:50.
In multiplying by 24 you get 37.833333, which is the value in number of
hours (not in Excel time format), which you should be formatting as
General
or Number. You seem to have multiplied by 24 but still tried to display
as
if it were an Excel time.
37.833333 is equivalent to 37 days and 20 hours, so in displaying as a
time
you are seeing the 20 hours as 20:00.
--
David Biddulph

That doesn't work for me. I'm using the help file's SUM(A2:A6)*24
because
it's more than 24 hours. I'm adding:

4:05
7:00
1:20
6:45
4:30
1:05
1:55
5:15
0:15
1:45
3:20
0:35

and I'm getting 20:00 as the result, which is obviously wrong. Why does
the
summation not work?

:

It's correct, it is whomever entered times as :mm:ss (leading colon)
that
made a mistake. Excel interprets this as text, so change the :42:39 to
0:42:39 and all the other entries that were made like that the same
way
and
you should get what you expect


--
Regards,

Peo Sjoblom



Thanx for the quick response. However, I tried using that format and
it
is
not adding up.

Here's what is happening:

1:03:06
:42:39
1:07:10
:55:54
:09:49
1:17:18
The sum is 3:27:34
--
which is incorrect it should be 5:15:56.

Please respond...
Barbara W


:

You didn't say what was appearing but yes, you probably need to
format
the total cell.
Format, Cell, Number, Time and select the most appropriate. Mine
has
a 37:30:55 option which should be OK for you.