Extracting Part Data From A Cell To Add In Another Cell

[ellipticrecords]

Hi There
I’m a newbie to Excel, self taught to date as I have started to find it a great program to work with. I have designed a schedule for use at work and it so far works out very simply hours required to do the tasks set. As you can see from the picture the hours columns are added up to created the total at row 18. What I would like to do is as the figures are added up the rest breaks are removed and added to row 20 automatically. So as an example:

Paul Chubb works 10hrs but has a 45min break in his shift, I therefore would like to place 10hrs in column D and in row 18 the 9hrs 15mins is added up and the 45min break is added to row 20.


Is there a way of doing this in Excel to save my adding up and time....


Thanks in advance.....

 

[ellipticrecords]

Think something might be wrong!!!!! Using the formula the breaks =4.8 though added on a calculator they =2.25. What would be causing this????

Looking forward......

 

[sijpie]

First of all check your formula: some of the ranges in it are D2:D17, one is D4:D17

For D4:D17 the result would be:
3 entries < 9: 3*0.5hrs = 1.5hrs
3 entries 9<= x <10.5: 3*0.75hrs = 2.25hrs
Total: 3.75hrs

With Row D2:D17
as above and
1 entry >10.5: 1*1hr=1hr
Total 4.75hrs

 

[ellipticrecords]

What a numpty i am, . Should have checked the obvious....Will go back and give it a whirl, thanks Sijpie