Adding up hours and minutes

You should EITHER use the formula:

=SUM(H10:H40)

and apply the custom format [h]:mm:ss to the cell if you want to
display the result like that and keep the total in Excel time format,
OR if you use the formula:

=SUM(H10:H40)*24

this will convert the result into (decimal) hours, so you need to
apply the format of Number with, say, 2 dp. With this formula and
format, an answer of, say, 26 hours and 45 minutes will be displayed
as 26.75.

Hope this helps.

Pete

On Jun 26, 1:19*pm, chieflx wrote:
Hi,
I seem to be having the same problem as Fly Boy Five, I have a list of times
(the cells are custom formatted as hh:mm) and need to add them up to give me
a monthly total. I have followed the help in excel 2002 and used the formula
=Sum(H10:h40)*24 and formated the cell to hh:mm to give me a total in cell
h41 but it only returns as 00:00. After reading the posts on this thread I
have tried different formats on the total cell but can not seem to get it to
work.

Any help would be much appreciated.

Many thanks in advance
Chieflx



"Sandy Mann" wrote:
Fly Boy Five,

Simply use =SUM(A1:A21) and format the cell as Custom format [h]:mm

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Fly Boy 5" wrote in message
...
I'm trying to add a column of hrs and minutes. *I tried to use the
=sum(a1:a21,"h:mm") *Don't work

What do you suggest?

Thanks Fly Boy Five

"Dave" wrote:

Hi,
I need to add up a list of hour and minutes, the only problem being is
that
Excel treats them as TIME not hours and minutes.

For example, if I sum 7:24, 7:24 , 7:24, 7:24 [ where 7 = hours and 24 =
minutes ] then Excel five me an answer of 05:36 rather than 29.36 - any
ideas
how I get this working ?

Thanks- Hide quoted text -

- Show quoted text -

Hi Pete,
Thanks for the quick response, I have tried formatting the cell as [h]:mm:ss
or [hh]:mm:ss and using the formula =SUM(H10:H40) but still get 0:00
returned. I have also tried =H10+h11+h12... with no luck.

I am sure I must be missing something simple but have been staring at it so
long my mind is going blank :-).

Do you know of any other ways to add a list of times?
Cheers
Chieflx

"Pete_UK" wrote:

You should EITHER use the formula:

=SUM(H10:H40)

and apply the custom format [h]:mm:ss to the cell if you want to
display the result like that and keep the total in Excel time format,
OR if you use the formula:

=SUM(H10:H40)*24

this will convert the result into (decimal) hours, so you need to
apply the format of Number with, say, 2 dp. With this formula and
format, an answer of, say, 26 hours and 45 minutes will be displayed
as 26.75.

Hope this helps.

Pete

On Jun 26, 1:19 pm, chieflx wrote:
Hi,
I seem to be having the same problem as Fly Boy Five, I have a list of times
(the cells are custom formatted as hh:mm) and need to add them up to give me
a monthly total. I have followed the help in excel 2002 and used the formula
=Sum(H10:h40)*24 and formated the cell to hh:mm to give me a total in cell
h41 but it only returns as 00:00. After reading the posts on this thread I
have tried different formats on the total cell but can not seem to get it to
work.

Any help would be much appreciated.

Many thanks in advance
Chieflx



"Sandy Mann" wrote:
Fly Boy Five,

Simply use =SUM(A1:A21) and format the cell as Custom format [h]:mm

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Fly Boy 5" wrote in message
...
I'm trying to add a column of hrs and minutes. I tried to use the
=sum(a1:a21,"h:mm") Don't work

What do you suggest?

Thanks Fly Boy Five

"Dave" wrote:

Hi,
I need to add up a list of hour and minutes, the only problem being is
that
Excel treats them as TIME not hours and minutes.

For example, if I sum 7:24, 7:24 , 7:24, 7:24 [ where 7 = hours and 24 =
minutes ] then Excel five me an answer of 05:36 rather than 29.36 - any
ideas
how I get this working ?

Thanks- Hide quoted text -

- Show quoted text -

Times are just numbers to Excel - it stores them as fractions of a 24-
hour day, so that 12:00:00 will be stored internally as 0.5, 6:00:00
(or 6 hours) as 0.25 etc. So, any of the normal arithmetic functions
SUM or + can be used with them. Perhaps you have entered the data as
text, so that it looks like a time but has no numeric value (so SUM
will give a result of zero).

Try formatting the cells as Number or General - if they are in Excel
time format you should see a load of fractional values, but if they
remain the same then they are really text values.

Hope this helps.

Pete

On Jun 26, 2:51*pm, chieflx wrote:
Hi Pete,
Thanks for the quick response, I have tried formatting the cell as [h]:mm:ss
or [hh]:mm:ss and using the formula =SUM(H10:H40) but still get 0:00
returned. I have also tried =H10+h11+h12... with no luck.

I am sure I must be missing something simple but have been staring at it so
long my mind is going blank :-).

Do you know of any other ways to add a list of times?
Cheers
Chieflx



"Pete_UK" wrote:
You should EITHER use the formula:

=SUM(H10:H40)

and apply the custom format [h]:mm:ss to the cell if you want to
display the result like that and keep the total in Excel time format,
OR if you use the formula:

=SUM(H10:H40)*24

this will convert the result into (decimal) hours, so you need to
apply the format of Number with, say, 2 dp. With this formula and
format, an answer of, say, 26 hours and 45 minutes will be displayed
as 26.75.

Hope this helps.

Pete

On Jun 26, 1:19 pm, chieflx wrote:
Hi,
I seem to be having the same problem as Fly Boy Five, I have a list of times
(the cells are custom formatted as hh:mm) and need to add them up to give me
a monthly total. I have followed the help in excel 2002 and used the formula
=Sum(H10:h40)*24 and formated the cell to hh:mm to give me a total in cell
h41 but it only returns as 00:00. After reading the posts on this thread I
have tried different formats on the total cell but can not seem to get it to
work.

Any help would be much appreciated.

Many thanks in advance
Chieflx

"Sandy Mann" wrote:
Fly Boy Five,

Simply use =SUM(A1:A21) and format the cell as Custom format [h]:mm

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Fly Boy 5" wrote in message
...
I'm trying to add a column of hrs and minutes. *I tried to use the
=sum(a1:a21,"h:mm") *Don't work

What do you suggest?

Thanks Fly Boy Five

"Dave" wrote:

Hi,
I need to add up a list of hour and minutes, the only problem being is
that
Excel treats them as TIME not hours and minutes.

For example, if I sum 7:24, 7:24 , 7:24, 7:24 [ where 7 = hours and 24 =
minutes ] then Excel five me an answer of 05:36 rather than 29.36 - any
ideas
how I get this working ?

Thanks- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Hi Pete,
Many thanks, I went back over the formulae I was using and had indeed
entered the time as text rather than numeric, it all works fine now.

If it is not too much of an imposition can I pick your brain one more time?

I now have all the time calculations but want to multiply them by an hourly
rate, ( I do not get to see my timesheet at work so like to keep my own copy
for reference once I get paid) do you know what function/formula I should use
to give me a monetary figure.

As you probably can guess I am fairly new to using excel and am trying to
teach myself as I go so any help is greatly appreciated.

Once again thank you for your help,

Cheers
Chieflx


"Pete_UK" wrote:

Times are just numbers to Excel - it stores them as fractions of a 24-
hour day, so that 12:00:00 will be stored internally as 0.5, 6:00:00
(or 6 hours) as 0.25 etc. So, any of the normal arithmetic functions
SUM or + can be used with them. Perhaps you have entered the data as
text, so that it looks like a time but has no numeric value (so SUM
will give a result of zero).

Try formatting the cells as Number or General - if they are in Excel
time format you should see a load of fractional values, but if they
remain the same then they are really text values.

Hope this helps.

Pete

On Jun 26, 2:51 pm, chieflx wrote:
Hi Pete,
Thanks for the quick response, I have tried formatting the cell as [h]:mm:ss
or [hh]:mm:ss and using the formula =SUM(H10:H40) but still get 0:00
returned. I have also tried =H10+h11+h12... with no luck.

I am sure I must be missing something simple but have been staring at it so
long my mind is going blank :-).

Do you know of any other ways to add a list of times?
Cheers
Chieflx



"Pete_UK" wrote:
You should EITHER use the formula:

=SUM(H10:H40)

and apply the custom format [h]:mm:ss to the cell if you want to
display the result like that and keep the total in Excel time format,
OR if you use the formula:

=SUM(H10:H40)*24

this will convert the result into (decimal) hours, so you need to
apply the format of Number with, say, 2 dp. With this formula and
format, an answer of, say, 26 hours and 45 minutes will be displayed
as 26.75.

Hope this helps.

Pete

On Jun 26, 1:19 pm, chieflx wrote:
Hi,
I seem to be having the same problem as Fly Boy Five, I have a list of times
(the cells are custom formatted as hh:mm) and need to add them up to give me
a monthly total. I have followed the help in excel 2002 and used the formula
=Sum(H10:h40)*24 and formated the cell to hh:mm to give me a total in cell
h41 but it only returns as 00:00. After reading the posts on this thread I
have tried different formats on the total cell but can not seem to get it to
work.

Any help would be much appreciated.

Many thanks in advance
Chieflx

"Sandy Mann" wrote:
Fly Boy Five,

Simply use =SUM(A1:A21) and format the cell as Custom format [h]:mm

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Fly Boy 5" wrote in message
...
I'm trying to add a column of hrs and minutes. I tried to use the
=sum(a1:a21,"h:mm") Don't work

What do you suggest?

Thanks Fly Boy Five

"Dave" wrote:

Hi,
I need to add up a list of hour and minutes, the only problem being is
that
Excel treats them as TIME not hours and minutes.

For example, if I sum 7:24, 7:24 , 7:24, 7:24 [ where 7 = hours and 24 =
minutes ] then Excel five me an answer of 05:36 rather than 29.36 - any
ideas
how I get this working ?

Thanks- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Let's say that you have a time in A1, in Excel time format, and an
hourly rate in B1. To get the cost in C1 you would need this formula:

=A1*B1*24

and you would need to format this cell as General, Number or Currency,
as Excel might assume you want it to be in time format.

Hope this helps.

Pete

On Jun 27, 5:57*am, chieflx wrote:
Hi Pete,
Many thanks, I went back over the formulae I was using and had indeed
entered the time as text rather than numeric, it all works fine now.

If it is not too much of an imposition can I pick your brain one more time?

I now have all the time calculations but want to multiply them by an hourly
rate, ( I do not get to see my timesheet at work so like to keep my own copy
for reference once I get paid) do you know what function/formula I should use
to give me a monetary figure.

As you probably can guess I am fairly new to using excel and am trying to
teach myself as I go so any help is greatly appreciated.

Once again thank you for your help,

Cheers
Chieflx



"Pete_UK" wrote:
Times are just numbers to Excel - it stores them as fractions of a 24-
hour day, so that 12:00:00 will be stored internally as 0.5, 6:00:00
(or 6 hours) as 0.25 etc. So, any of the normal arithmetic functions
SUM or + can be used with them. Perhaps you have entered the data as
text, so that it looks like a time but has no numeric value (so SUM
will give a result of zero).

Try formatting the cells as Number or General - if they are in Excel
time format you should see a load of fractional values, but if they
remain the same then they are really text values.

Hope this helps.

Pete

On Jun 26, 2:51 pm, chieflx wrote:
Hi Pete,
Thanks for the quick response, I have tried formatting the cell as [h]:mm:ss
or [hh]:mm:ss and using the formula =SUM(H10:H40) but still get 0:00
returned. I have also tried =H10+h11+h12... with no luck.

I am sure I must be missing something simple but have been staring at it so
long my mind is going blank :-).

Do you know of any other ways to add a list of times?
Cheers
Chieflx

"Pete_UK" wrote:
You should EITHER use the formula:

=SUM(H10:H40)

and apply the custom format [h]:mm:ss to the cell if you want to
display the result like that and keep the total in Excel time format,
OR if you use the formula:

=SUM(H10:H40)*24

this will convert the result into (decimal) hours, so you need to
apply the format of Number with, say, 2 dp. With this formula and
format, an answer of, say, 26 hours and 45 minutes will be displayed
as 26.75.

Hope this helps.

Pete

On Jun 26, 1:19 pm, chieflx wrote:
Hi,
I seem to be having the same problem as Fly Boy Five, I have a list of times
(the cells are custom formatted as hh:mm) and need to add them up to give me
a monthly total. I have followed the help in excel 2002 and used the formula
=Sum(H10:h40)*24 and formated the cell to hh:mm to give me a total in cell
h41 but it only returns as 00:00. After reading the posts on this thread I
have tried different formats on the total cell but can not seem to get it to
work.

Any help would be much appreciated.

Many thanks in advance
Chieflx

"Sandy Mann" wrote:
Fly Boy Five,

Simply use =SUM(A1:A21) and format the cell as Custom format [h]:mm

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Fly Boy 5" wrote in message
...
I'm trying to add a column of hrs and minutes. *I tried to use the
=sum(a1:a21,"h:mm") *Don't work

What do you suggest?

Thanks Fly Boy Five

"Dave" wrote:

Hi,
I need to add up a list of hour and minutes, the only problem being is
that
Excel treats them as TIME not hours and minutes.

For example, if I sum 7:24, 7:24 , 7:24, 7:24 [ where 7 = hours and 24 =
minutes ] then Excel five me an answer of 05:36 rather than 29.36 - any
ideas
how I get this working ?

Thanks- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thank you very much for that Pete, it all works beautifully and I have
already found I have been underpaid this month!!

All the best
Chieflx

"Pete_UK" wrote:

Let's say that you have a time in A1, in Excel time format, and an
hourly rate in B1. To get the cost in C1 you would need this formula:

=A1*B1*24

and you would need to format this cell as General, Number or Currency,
as Excel might assume you want it to be in time format.

Hope this helps.

Pete

On Jun 27, 5:57 am, chieflx wrote:
Hi Pete,
Many thanks, I went back over the formulae I was using and had indeed
entered the time as text rather than numeric, it all works fine now.

If it is not too much of an imposition can I pick your brain one more time?

I now have all the time calculations but want to multiply them by an hourly
rate, ( I do not get to see my timesheet at work so like to keep my own copy
for reference once I get paid) do you know what function/formula I should use
to give me a monetary figure.

As you probably can guess I am fairly new to using excel and am trying to
teach myself as I go so any help is greatly appreciated.

Once again thank you for your help,

Cheers
Chieflx



"Pete_UK" wrote:
Times are just numbers to Excel - it stores them as fractions of a 24-
hour day, so that 12:00:00 will be stored internally as 0.5, 6:00:00
(or 6 hours) as 0.25 etc. So, any of the normal arithmetic functions
SUM or + can be used with them. Perhaps you have entered the data as
text, so that it looks like a time but has no numeric value (so SUM
will give a result of zero).

Try formatting the cells as Number or General - if they are in Excel
time format you should see a load of fractional values, but if they
remain the same then they are really text values.

Hope this helps.

Pete

On Jun 26, 2:51 pm, chieflx wrote:
Hi Pete,
Thanks for the quick response, I have tried formatting the cell as [h]:mm:ss
or [hh]:mm:ss and using the formula =SUM(H10:H40) but still get 0:00
returned. I have also tried =H10+h11+h12... with no luck.

I am sure I must be missing something simple but have been staring at it so
long my mind is going blank :-).

Do you know of any other ways to add a list of times?
Cheers
Chieflx

"Pete_UK" wrote:
You should EITHER use the formula:

=SUM(H10:H40)

and apply the custom format [h]:mm:ss to the cell if you want to
display the result like that and keep the total in Excel time format,
OR if you use the formula:

=SUM(H10:H40)*24

this will convert the result into (decimal) hours, so you need to
apply the format of Number with, say, 2 dp. With this formula and
format, an answer of, say, 26 hours and 45 minutes will be displayed
as 26.75.

Hope this helps.

Pete

On Jun 26, 1:19 pm, chieflx wrote:
Hi,
I seem to be having the same problem as Fly Boy Five, I have a list of times
(the cells are custom formatted as hh:mm) and need to add them up to give me
a monthly total. I have followed the help in excel 2002 and used the formula
=Sum(H10:h40)*24 and formated the cell to hh:mm to give me a total in cell
h41 but it only returns as 00:00. After reading the posts on this thread I
have tried different formats on the total cell but can not seem to get it to
work.

Any help would be much appreciated.

Many thanks in advance
Chieflx

"Sandy Mann" wrote:
Fly Boy Five,

Simply use =SUM(A1:A21) and format the cell as Custom format [h]:mm

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Fly Boy 5" wrote in message
...
I'm trying to add a column of hrs and minutes. I tried to use the
=sum(a1:a21,"h:mm") Don't work

What do you suggest?

Thanks Fly Boy Five

"Dave" wrote:

Hi,
I need to add up a list of hour and minutes, the only problem being is
that
Excel treats them as TIME not hours and minutes.

For example, if I sum 7:24, 7:24 , 7:24, 7:24 [ where 7 = hours and 24 =
minutes ] then Excel five me an answer of 05:36 rather than 29.36 - any
ideas
how I get this working ?

Thanks- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Well, that's good to hear !!

Thanks for the feed back - always appreciated.

Pete

On Jun 27, 11:35*am, chieflx
wrote:
Thank you very much for that Pete, it all works beautifully and I have
already found I have been underpaid this month!!

All the best
Chieflx

I have been reading the answers to hour/minute type questions but can't seem
to find the answer I need...

I am trying to calculate total hours worked in during a night shift like this:

A1 = 02/05/10 8:30 PM
B1 = 2/06/10 7:00 AM
C1 = B1-A1 = 10.30

I would like the 10.30 to appear as 10.5 instead.

Could anyone help me with this?

Trina C.
"Pete_UK" wrote:

Use a custom format for the total cell:

[h]:mm

will stop hours above 25 being treated as days.

The same can be accomplished by choosing the format "37:30:55" from the
list provided under Format | Cells | Number (tab) | Time.

Hope this helps.

Pete

Dave wrote:
Hi,
I need to add up a list of hour and minutes, the only problem being is that
Excel treats them as TIME not hours and minutes.

For example, if I sum 7:24, 7:24 , 7:24, 7:24 [ where 7 = hours and 24 =
minutes ] then Excel five me an answer of 05:36 rather than 29.36 - any ideas
how I get this working ?

Thanks

It won't be 10.30, it will be 10:30. There is a difference between a colon
and a decimal point.
If you want to turn it to decimal hours, multiply by 24 and format as
general or number instead of time.
--
David Biddulph

Tacrier wrote:
I have been reading the answers to hour/minute type questions but
can't seem to find the answer I need...

I am trying to calculate total hours worked in during a night shift
like this:

A1 = 02/05/10 8:30 PM
B1 = 2/06/10 7:00 AM
C1 = B1-A1 = 10.30

I would like the 10.30 to appear as 10.5 instead.

Could anyone help me with this?

Trina C.
"Pete_UK" wrote:

Use a custom format for the total cell:

[h]:mm

will stop hours above 25 being treated as days.

The same can be accomplished by choosing the format "37:30:55" from
the list provided under Format | Cells | Number (tab) | Time.

Hope this helps.

Pete

Dave wrote:
Hi,
I need to add up a list of hour and minutes, the only problem being
is that Excel treats them as TIME not hours and minutes.

For example, if I sum 7:24, 7:24 , 7:24, 7:24 [ where 7 = hours and
24 = minutes ] then Excel five me an answer of 05:36 rather than
29.36 - any ideas how I get this working ?

Thanks

Thank you David (sorry for the late reply!)



"David Biddulph" wrote:

It won't be 10.30, it will be 10:30. There is a difference between a colon
and a decimal point.
If you want to turn it to decimal hours, multiply by 24 and format as
general or number instead of time.
--
David Biddulph

Tacrier wrote:
I have been reading the answers to hour/minute type questions but
can't seem to find the answer I need...

I am trying to calculate total hours worked in during a night shift
like this:

A1 = 02/05/10 8:30 PM
B1 = 2/06/10 7:00 AM
C1 = B1-A1 = 10.30

I would like the 10.30 to appear as 10.5 instead.

Could anyone help me with this?

Trina C.
"Pete_UK" wrote:

Use a custom format for the total cell:

[h]:mm

will stop hours above 25 being treated as days.

The same can be accomplished by choosing the format "37:30:55" from
the list provided under Format | Cells | Number (tab) | Time.

Hope this helps.

Pete

Dave wrote:
Hi,
I need to add up a list of hour and minutes, the only problem being
is that Excel treats them as TIME not hours and minutes.

For example, if I sum 7:24, 7:24 , 7:24, 7:24 [ where 7 = hours and
24 = minutes ] then Excel five me an answer of 05:36 rather than
29.36 - any ideas how I get this working ?

Thanks


.