Solved

Formula / Macro required to calculate hours in arrears per job

Posted on 2013-07-01
4
473 Views
Last Modified: 2013-07-02
Hi Guys

I know you can help me on this

I have a planning sheet that lists each job situated in certain bays / areas. Each process is allocated a set amount of hours and against them, on the plan, is a letter signifying the type of resource required, eg, E for electrician so on so forth.

Each day the plant works 8 hours so each square on the plan is 8 hours worth,  Now the first thing you'll notcie is that the planned hours sometimes does not relate wholly to the amount of shaded squares.  This im not bothered as I only want to use the shaded squares to get my result.

So, in row E is the actual dates per day off which another piece of code (Kindly provided by another expert on here) that deals with holidays etc.

For this exercise, could someone help me with a formula, array, or code that will look in each job section, just out to December this year will be fine, and say if today is 1st July, count how many shaded lettered squares are in this section, under previous dates, and multiply them by 8 to give me hours / days in arrears.

Basically, we havent had a job go out on time and its the trigger to justify spending the overtime versus customer satisfaction so its quite cruicial.

I tried a few ways but to avail so any help I would very much appreciate

I have attached the actual sheet for your viewing pleasure ;o))

Thanks
J
Production-Plan-21-06-2013.xlsm
0
Comment
Question by:spicecave
  • 2
  • 2
4 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 39291459
Jase,
I found this question confusing as to what you wanted. So I guessed that you want to calculate the number of hours by trade represented by letters representing that trade occurring before the date in row 6 (i.e. in columns to the left of). This formula assumes that letters will be erased when tasks are completed.

If I have completely missed the boat, could you please give a sample calculation (with correct answer) showing you want for one trade and date?


Assuming I had guessed correctly, I copied the trade initials and names to A280:B290. I then put the following formula in F280 and copied across and down:
=SUM(E280,COUNTIF(E$7:E$278,$A280)*IF(E$6="S",4,8))

This formula keeps a running total of the number of backlog hours (8 on weekdays, 4 on Saturday) by trade and date.

Hours for the current day are not included in the backlog. If you want current day hours included in the backlog, change the formula to:
=SUM(E280,COUNTIF(F$7:F$278,$A280)*IF(F$6="S",4,8))

I also had to make a change to the Worksheet_Change sub because it kept triggering when I was creating the backlog formulas. The statement setting the range for targ should be changed to:
Set targ = Range("F7:FX" & Range("B7").End(xlDown).Row)  'Watch these cells for holiday conflicts

Brad
Production-Plan-21-06-2013Q28172.xlsm
0
 

Author Comment

by:spicecave
ID: 39292419
HI Brad

Apologies for the description - it was a bit convoluted.

Your system is actually better than the concept I suggested and I will definitely be keeping this as part of the sheet.

I have re-attached the original sheet with the result of what I originally conceived. Obviously, on the plan, there are several jobs on each of the bays (eg, Keltbray Mega Railer, Atlas 1604, Flashbutt Welder etc.) These are all separate builds on each area (or bay), with the breakdown of the processes and allocated hours for each process (eg. PPI Machine, Rip Out Old Electrics etc.)

All I need for each job section (in this example I have used the Keltbray Mega Railer) is to look at any trade letters still evident on the plan before todays date and as stated, taking each day as 8 hours, put a calculation on each job header saying "Hours in Arrears" and put in a formula counting the number of trade letters multiplied by 8.  In this example, taking today's date, before this, there are 24 'jobs' in arrears (F x 2, WF x 8, HF x 11, EM x 8) which calucaltes to 24 x 8 = 192 hours in arrears for the Keltbray Mega Railer job. I have put '8' over the jobs that justify the calculation.

Is this OK ? Would you be able to re-look at this as I would ideally like to have this AND the cumulative calc you orginally set up as part of this as the more information I have to deal with various queries, the better.

Cheers
Jase
Production-Plan-21-06-2013-EESam.xlsm
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39295274
Jase,
I needed to convert your header labels into real dates. So row 4 has Custom format "Week Starting "d mmmm and row 6 has Custom format ddd (with Arial Narrow font).

I could then track the hours in arrear using a formula like:
=SUMPRODUCT((F19:HG48<>"")*(F$6:HG$6<TODAY())*(8-4*(WEEKDAY(F$6:HG$6)=7)))
Note that you will need to change the range F19:HG48 each time you paste the formula.

You may prefer to use a somewhat longer (but computationally more efficient) formula that can be copied down, instead of changing the range F19:HG48 in each cell receiving the formula. Doing so requires adding an auxiliary column (column D in the example worksheet) with an "L" placed to mark the end of the project being studied for arrears. You can then calculate the arrears using the following formula in cell E18 (which may be copied down):
=IF(C18<>"Hrs Arrears","",SUMPRODUCT((G19:INDEX(HH19:HH$1000,MATCH("L",D19:D$1000,0))<>"")*(G$6:HH$6<TODAY())*(8-4*(WEEKDAY(G$6:HH$6)=7))))
This formula returns the number of hours of arrears if column C contains "Hrs Arrears"; if not, it returns an empty string (looks like a blank).

In the above formula, the reference to row 1000 is arbitrary. Make sure it extends beyond the end of your data.

The sample workbook contains your original spreadsheet layout in worksheet "Production Plan" with the first formula. It contains the second formula in worksheet "Production Plan (alt)".

In both cases, I only put the formula in one cell (where you had added up 192 hours). I trust that you will have no problem copying it down, making revisions as necessary.
Production-Plan-21-06-2013-Q2817.xlsm
0
 

Author Closing Comment

by:spicecave
ID: 39295686
Hi Brad

Once again - outstanding !

Many thanks for your continued support it is very much appreciated.

Regards
Jase
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now