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.....

 

[sijpie]

Not too difficult, depending on how the breaks are organised. Is it 45 minutes regardless of the hours worked, or is there a different distribution?

If it is 45 minutes regardless, then in row 20 the formula could be:

=COUNT(D2:D17)*0.75

andi in row 18:
=SUM(D2:D17)-D20

 

[ellipticrecords]

Thanks for the reply. There are different breaks. So a 10 hour shift would have a 45min break, other breaks vary so a 8.5hr shift is only 15mins. Where a 9hr shift is imputed this still has a 45min break attached to it as it is a "drop hour" to keep the worked hours at 39per week...

Thanks, looking forward.

 

[sijpie]

Hey I am glad I am not working there! 15min break for 8.5 hours, gee wiz how generous.

Anyway back to the problem. You will have to amend the formula to suit:

in D20:
=COUNTIF(D2:D17,"<9")*0.25+(COUNTIF(D2:D17,"<=10.5")-COUNTIF(D2:D17,"<9"))*0.75+COUNTIF(D2:D17,">10.5")*1

would give
.25 hrs to Less than 9 hrs worked
.75 hrs to between 9 and 10.5 hrs
1 hrs to more than 10.5 hrs worked

 

[ellipticrecords]

Sorry i got a bit wrong, its a 30min break in an 8.5 hr shift, not 15mins.......

 

[wlof]

I had something similar.

code for breaks for me

=IF(c3<6,0,IF(c3)<7,15,30)/60

 

[sijpie]

ellepticrecords, did the formula work (replacing the 0.25 with 0.5)?

 

[ellipticrecords]

Hi Sijpie, the formula seems to work fine. Can you just break it down and explain a little so i get an understanding of the formula and how it works. Thanks for your help...

 

[sijpie]

OK. In 18 you calculate the total hours and substract the breaks which are calculated in 20.

In 20 we have this formula:
=COUNTIF(D2:D17,"<9")*0.5+(COUNTIF(D2:D17,"<=10.5")-COUNTIF(D2:D17,"<9"))*0.75+COUNTIF(D2:D17,">10.5")*1

First part for hours worked less than 9 hours:
=COUNTIF(D2:D17,"<9")*0.5
Count the occurances of less than 9 hours. These get 0.5 hour break. So if there are 3, then there is 3*0.5 - 1.5 hrs break.

Next part is for those hours from 9 (inclusive) to 10.5 hours (inclusive), each good for 0/75 hrs break:
+(COUNTIF(D2:D17,"<=10.5")-COUNTIF(D2:D17,"<9"))*0.75
COunt number of occurances of 10.5 hrs or less, substract occurances of less than 9 hrs. Then multiply with 0.75

Last for more than 10.5 grs worked, I assumed one hour break:
+COUNTIF(D2:D17,">10.5")*1

So if the real numbers are different, then you can now correct, or add more categories.

 

[ellipticrecords]

Thats great, thank you so much for all your help and the learn around Excel. I can see now how it works, if i run in to any more trouble i shall return....This clearly is a great forum and im glad i found it...Again Sijpie thanks for all your help and have a very Happy Xmas and a great New Year.......