Extracting Part Data From A Cell To Add In Another Cell

ellipticrecords

New Member
Joined
Dec 4, 2010
Messages
7
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,527
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
Joined
Dec 4, 2010
Messages
7
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
Joined
Nov 1, 2008
Messages
3,527
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
Joined
Dec 4, 2010
Messages
7
Sorry i got a bit wrong, its a 30min break in an 8.5 hr shift, not 15mins.......:LOL:
 

wlof

New Member
Joined
Mar 3, 2010
Messages
5
I had something similar.

code for breaks for me

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

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,527
ellepticrecords, did the formula work (replacing the 0.25 with 0.5)?
 

ellipticrecords

New Member
Joined
Dec 4, 2010
Messages
7
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
Joined
Nov 1, 2008
Messages
3,527
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
Joined
Dec 4, 2010
Messages
7
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.......:)
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top