# Extracting Part Data From A Cell To Add In Another Cell

#### ellipticrecords

##### New Member
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....

#### sijpie

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

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

#### wlof

##### New Member

code for breaks for me

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

#### sijpie

##### Well-known Member
ellepticrecords, did the formula work (replacing the 0.25 with 0.5)?

#### ellipticrecords

##### New Member
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

##### Well-known Member
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

##### New Member
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.......

1,081,838
Messages
5,361,609
Members
400,641
Latest member
Scotty82

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...