sum time

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

How do you add the following times:

8:10
8:15
8:00
8:30
7:40

The total amount of hours is 40:35
What formula would do this?

Thanking you if you can help

Pat
 
Hi Pat
just simply use the SUM function and format the target cell with the
custom format
[hh]:mm
 
Frank, I should have mentioned the format part, but the result is
nevertheless the same.
Where does the result 16:35 come from? There is obviously something not
right.

Pat

Frank Kabel said:
Hi
you forgot the formating part :-)

- select this cell
- goto 'Format - Cells - Numbers'
- choose the custom category
- enter the format: [hh]:mm

the [hh] part will prevent the rollover after 24 hours


--
Regards
Frank Kabel
Frankfurt, Germany
Hello Frank

I don't know why I cannot get the SUM function to work for me

A1 8:10
A2 8:15
A3 8:00
A4 8:30
A5 7:40

When I use the sum function
=SUM(A1:A5)

This returns 16:35 when in fact it should return 40:25

What might be the problem here?

Pat



Frank Kabel said:
Hi Pat
just simply use the SUM function and format the target cell with the
custom format
[hh]:mm

--
Regards
Frank Kabel
Frankfurt, Germany

Pat wrote:
How do you add the following times:

8:10
8:15
8:00
8:30
7:40

The total amount of hours is 40:35
What formula would do this?

Thanking you if you can help

Pat
Click to expand...
Click to expand...
Click to expand...
 
Hello Frank

I don't know why I cannot get the SUM function to work for me

A1 8:10
A2 8:15
A3 8:00
A4 8:30
A5 7:40

When I use the sum function
=SUM(A1:A5)

This returns 16:35 when in fact it should return 40:25

What might be the problem here?

Pat



Frank Kabel said:
Hi Pat
just simply use the SUM function and format the target cell with the
custom format
[hh]:mm

--
Regards
Frank Kabel
Frankfurt, Germany
How do you add the following times:

8:10
8:15
8:00
8:30
7:40

The total amount of hours is 40:35
What formula would do this?

Thanking you if you can help

Pat
Click to expand...
Click to expand...
 
Hi
you forgot the formating part :-)

- select this cell
- goto 'Format - Cells - Numbers'
- choose the custom category
- enter the format: [hh]:mm

the [hh] part will prevent the rollover after 24 hours


--
Regards
Frank Kabel
Frankfurt, Germany
Hello Frank

I don't know why I cannot get the SUM function to work for me

A1 8:10
A2 8:15
A3 8:00
A4 8:30
A5 7:40

When I use the sum function
=SUM(A1:A5)

This returns 16:35 when in fact it should return 40:25

What might be the problem here?

Pat



Frank Kabel said:
Hi Pat
just simply use the SUM function and format the target cell with the
custom format
[hh]:mm

--
Regards
Frank Kabel
Frankfurt, Germany
How do you add the following times:

8:10
8:15
8:00
8:30
7:40

The total amount of hours is 40:35
What formula would do this?

Thanking you if you can help

Pat
Click to expand...
Click to expand...
Click to expand...
 
Hi
if you apply this format as cutsom format (including the brackets []
around hh) you'll get 40:35
Your result of 16:35 comes from the following:
- without the custom format Excel starts counting again from zero after
reaching 24 hours. So in your case it shows
40:35 - 24:00 = 16:35



--
Regards
Frank Kabel
Frankfurt, Germany
Frank, I should have mentioned the format part, but the result is
nevertheless the same.
Where does the result 16:35 come from? There is obviously something
not right.

Pat

Frank Kabel said:
Hi
you forgot the formating part :-)

- select this cell
- goto 'Format - Cells - Numbers'
- choose the custom category
- enter the format: [hh]:mm

the [hh] part will prevent the rollover after 24 hours


--
Regards
Frank Kabel
Frankfurt, Germany
Hello Frank

I don't know why I cannot get the SUM function to work for me

A1 8:10
A2 8:15
A3 8:00
A4 8:30
A5 7:40

When I use the sum function
=SUM(A1:A5)

This returns 16:35 when in fact it should return 40:25

What might be the problem here?

Pat



Hi Pat
just simply use the SUM function and format the target cell with
the custom format
[hh]:mm

--
Regards
Frank Kabel
Frankfurt, Germany

Pat wrote:
How do you add the following times:

8:10
8:15
8:00
8:30
7:40

The total amount of hours is 40:35
What formula would do this?

Thanking you if you can help

Pat
Click to expand...
Click to expand...
Click to expand...
 
The reason you get 16:35 is that the default format uses 24 hours and
40:35 - 16:35 is 24:00
or 40:35 - 24:00 is 16:35
You need to use format>cells>number>custom and [hh]:mm or else it won't roll
over,
this is just display the underlying value is 40:35, you can test that by
multiplying by 24 and
format as general which will return the decimal value 40.5833333333333

Don't format the values you are summing, format result and you should be
alright

--

Regards,

Peo Sjoblom

Pat said:
Frank, I should have mentioned the format part, but the result is
nevertheless the same.
Where does the result 16:35 come from? There is obviously something not
right.

Pat

Frank Kabel said:
Hi
you forgot the formating part :-)

- select this cell
- goto 'Format - Cells - Numbers'
- choose the custom category
- enter the format: [hh]:mm

the [hh] part will prevent the rollover after 24 hours


--
Regards
Frank Kabel
Frankfurt, Germany
Hello Frank

I don't know why I cannot get the SUM function to work for me

A1 8:10
A2 8:15
A3 8:00
A4 8:30
A5 7:40

When I use the sum function
=SUM(A1:A5)

This returns 16:35 when in fact it should return 40:25

What might be the problem here?

Pat



Hi Pat
just simply use the SUM function and format the target cell with the
custom format
[hh]:mm

--
Regards
Frank Kabel
Frankfurt, Germany

Pat wrote:
How do you add the following times:

8:10
8:15
8:00
8:30
7:40

The total amount of hours is 40:35
What formula would do this?

Thanking you if you can help

Pat
Click to expand...
Click to expand...
Click to expand...
 
Silly of me not to see the brackets in your original answer.

Cheers


Frank Kabel said:
Hi
if you apply this format as cutsom format (including the brackets []
around hh) you'll get 40:35
Your result of 16:35 comes from the following:
- without the custom format Excel starts counting again from zero after
reaching 24 hours. So in your case it shows
40:35 - 24:00 = 16:35



--
Regards
Frank Kabel
Frankfurt, Germany
Frank, I should have mentioned the format part, but the result is
nevertheless the same.
Where does the result 16:35 come from? There is obviously something
not right.

Pat

Frank Kabel said:
Hi
you forgot the formating part :-)

- select this cell
- goto 'Format - Cells - Numbers'
- choose the custom category
- enter the format: [hh]:mm

the [hh] part will prevent the rollover after 24 hours


--
Regards
Frank Kabel
Frankfurt, Germany

Pat wrote:
Hello Frank

I don't know why I cannot get the SUM function to work for me

A1 8:10
A2 8:15
A3 8:00
A4 8:30
A5 7:40

When I use the sum function
=SUM(A1:A5)

This returns 16:35 when in fact it should return 40:25

What might be the problem here?

Pat



Hi Pat
just simply use the SUM function and format the target cell with
the custom format
[hh]:mm

--
Regards
Frank Kabel
Frankfurt, Germany

Pat wrote:
How do you add the following times:

8:10
8:15
8:00
8:30
7:40

The total amount of hours is 40:35
What formula would do this?

Thanking you if you can help

Pat
Click to expand...
Click to expand...
Click to expand...
 
Back
Top