Summation of time

[Bikertyke]

Why when a try to sum a number of time entries on a spreadsheet who's total
should be around 192 (worked hours per month) does the total appear as 00:00.
This is despite uning the [hh]:mm format, timesing the whole by 24 and
numerous other 'ideas'
Any help greatly apreciated.

 

[SteveW]

Divide, not times

cells with say 8,8.5,7,9.25,10 ... etc will add up to a figure like 192

Cells need to be in fractions of a day to ie 8/24, 9/24 to be able to
display them as hh:mm

If you are happy with the individual ones being shown as decimal hours, ie
8.5
rather than 8:30 - leave these cells as they are.

The sum cell just needs to be converted
so should be =sum(CellWithHoursValues)/24

Steve

 

[Roger Govier]

Hi

If you are summing Excel times, then the cell with the formula need to
be formatted as [h]:mm to allow it to cycle past 24 hours.

 

[Bikertyke]

It appears that Excel will not sum a column of calculated times. My time
column is the product of my start time and finish time minus my lunch time
and therefore a calculation rather than a straight forward number. It works
fine if I enter the pure number but thats not what a spreadsheet is all
about.....is it?
Is this usual.

Why when a try to sum a number of time entries on a spreadsheet who's
total
should be around 192 (worked hours per month) does the total appear as
00:00.
This is despite uning the [hh]:mm format, timesing the whole by 24 and
numerous other 'ideas'
Any help greatly apreciated.

 

[Bob Phillips]

Summing times works fine.

If you enter real times (8:15:14:30, etc.) and format the total cell as
[h]:mm it will show the result you expect.

What does your data look like?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

It appears that Excel will not sum a column of calculated times. My time
column is the product of my start time and finish time minus my lunch time
and therefore a calculation rather than a straight forward number. It works
fine if I enter the pure number but thats not what a spreadsheet is all
about.....is it?
Is this usual.

Why when a try to sum a number of time entries on a spreadsheet who's
total
should be around 192 (worked hours per month) does the total appear as
00:00.
This is despite uning the [hh]:mm format, timesing the whole by 24 and
numerous other 'ideas'
Any help greatly apreciated.

 

[Roger Govier]

What formula are you using? It should work fine.

With start in A1, Finish in B1 and lunch 1 hour and assuming entries of
9:00 and 17:00 then in C1
=B1-A1-TIME(1,0,0)
should return a value of 7:00
Copied down column C and then with a summation in cell C32
=SUM(C1:C31) with a format of [h]:mm should produce your result of
192:00 or whatever.

If you wanted it as decimal time, then
=(B1-A1-TIME(1,0,0))*24 with a format of General, will return 7 and C32
formatted as General would return 192.

--
Regards

Roger Govier

It appears that Excel will not sum a column of calculated times. My
time
column is the product of my start time and finish time minus my lunch
time
and therefore a calculation rather than a straight forward number. It
works
fine if I enter the pure number but thats not what a spreadsheet is
all
about.....is it?
Is this usual.

Why when a try to sum a number of time entries on a spreadsheet
who's
total
should be around 192 (worked hours per month) does the total appear
as
00:00.
This is despite uning the [hh]:mm format, timesing the whole by 24
and
numerous other 'ideas'
Any help greatly apreciated.

 

[SteveW]

Did you see my post ?

Steve

It appears that Excel will not sum a column of calculated times. My time
column is the product of my start time and finish time minus my lunch
time
and therefore a calculation rather than a straight forward number. It
works
fine if I enter the pure number but thats not what a spreadsheet is all
about.....is it?
Is this usual.

Why when a try to sum a number of time entries on a spreadsheet who's
total
should be around 192 (worked hours per month) does the total appearas
00:00.
This is despite uning the [hh]:mm format, timesing the whole by 24 and
numerous other 'ideas'
Any help greatly apreciated.

 

[Bikertyke]

Gents
Your all heroes. Thanks for the assistance.