# How to calculate the days ..Pl guide me

Discussion in 'Business Applications' started by Gupta, Sep 15, 2010.

Not open for further replies.

Joined:
Sep 10, 2010
Messages:
7
Dear all, I am working in an organization & looking production planning.I manage the entire controls thru excel sheets.Our per day Volumes are as high as 15000 units per day and managing the material and tracking dates associated with orders is being done thru seperate excel sheets but I am looking to create new excel tracker to make things simpler.In this exercise I came acorss with a requirement.For all you learned boarders I am attaching a sample work sheet for reference.I would be much delight full if you help me out to overcome this problem.I have mentioned the requirements in worksheet itself.

Looking for an early response

#### Attached Files:

• ###### test file Tech Guys.xls
File size:
24.5 KB
Views:
59

Joined:
Sep 10, 2010
Messages:
7
Dear all I was having hope that someone wll come up and provide me solution but perhaps no bobody could spare time.Pl help asap

3. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
I entered this in P4:

=OFFSET(\$A\$1,ROW()-1,MATCH(MID(\$P\$3,FIND("order",\$P\$3),256),\$A\$3:\$AK\$3,0)-1)

This determines the order number from row 3 (example "order 1" in P3), then looks for the first occurrence of that in row 3 (B3), then uses OFFSET to get the value from row 4 of the identified column (that is, B4 -- which I believe represents qty of XYZ of order 1).

You can test the formula by changing B4 to (example) 500 and P4 will change to match.

That's the first step, unless I'm mistaken.

For the second step, you want to determine at what point the sum of the range starting at W4 and continuing to the right reaches (or exceeds) 95% of P4. By "at what point" I mean which column. Is that correct?

Joined:
Sep 10, 2010
Messages:
7
Thanx Bomb ..for sparing time.I could not do anything by the formula prescribed by you as it is showing the error.Perhaps I went wrong somewhere to describe my requirement so let me explain the requirement once again.In my sample sheet I keep on recording the incoming order's qty in columns from column B,C,D,E in this case.For every incoming order I generate respective columns to note order receiving dates (F,G,H,I, in this case ).After that I add columns giving me dates for order delivery/completion dates by adding 14 days lead time (J,K,L,M in this case).To summarise cell B4 has order qty for model XYZ in cell A4,F4 has order1 receiving date and J4 has its expected completion date.
Now when we start executing the various orders I keep on noting the qtys in column W4 to AK4 and total production qty is summed up in cell N4.I want when 95% of order 1 qty is complete the P4 should give me the no of days taken to complete the 95% of order one.Say I make 50 nos of model XYZ on 1-sep and note it down in W4,, 20 nos on 5-sep and note it down in AA4and remaining 25 nos on 8-sep and note it down AD4 then cell P4 should show the number of days taken to complete the order with respect to order receiving date.In described example order receiving date is 15-jul-2010 and completion date is 8-sep so P4 show 55 days as a differenece of both dates.Further if I make and complete last 25 nos on 15-sep then P4 should show 62.Hope this time I am able to describe my requirement clearly.If my this problem is solved I will be able to utilize my precious time in some more fruitful jobs.Looking forward for support.

Joined:
Sep 10, 2010
Messages:
7
I thoght I would support from this forum to resolve the problem.But it's mine HARD LUCK.

6. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
The formula works for me, perhaps you entered it wrong.

Anyway, it occurred to me that it's maybe (even) more complicated. W4 is 50, but how does Excel know that relates to Order 1? Is X4 (20) also Order 1, or is it Order 2? And so on.

To me it seems you really need a separate "Production" table -- Production Date/Order Number/Qty.

7. ### turbodante

Joined:
Dec 19, 2008
Messages:
744
I fear that if you're going to keep adding columns to accomodate the extra dates for production it will get more complicated as well as running the risk of running out of columns, are you sure this is the way to go?

8. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
See Sheet1 of the attached.

Columns A to C are for updating as and when you "make nos".

Columns D to F are formulas. They could be combined into fewer columns, I've done them "simpler" for clarity.

Column I shows "overall" qty. Column J shows the date from column A for the first time column F shows "Order *". Thus column K is "first production date to [first production >=95%]" inclusive.

If B6 is "Order 2" and C6 is 12, K2 = #N/A because 24 (D6, the running total for Order 2) is less than E6 (95% of I3).

But change C6 to 13, then D6 is greater than E6 so K3 shows 5 (17 Sept to 21 Sept inclusive for Order 2).

This might need expanding to accommodate Model No, but in general it's a better way to go about it, I feel.

HTH

#### Attached Files:

• ###### gupta.xls
File size:
27.5 KB
Views:
45

Joined:
Sep 10, 2010
Messages:
7
Hi Bomb... Thanks for the support you extended but I could not achieved my target.Now I have decided the other way as I am not in a postion to change the recording format.I inserted two helper column and put some VBA to throw date in column Q.Now what I do is that after seeing the status in column " O" as completion I write complete as text in column " P" and VBA throws the STATIC date in column " Q ".Then I will calculate the days taken to complete the order wrt to order receiving date in seperate column.I will use same method for order 2 similarly in seperate set of columns as yoiu see in sheet.So just help me on column "Q".Help required is that can we throw static date in column "Q" based upon results of column "O" means as soon as condition of column is fulfilled and text COMPLETE is written by formulae then date should also be thrown in column "Q" and I do not need to write it manually.If this problem is solved my problem is over.I have used below VBA inn the attached sheet.
So pl help me to get rid of the issue as I am fighting with it for long now.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("P").Column Then
Cells(.Row, "Q").Value = Int(Now)
End If
End With
Next Cell
End Sub

Joined:
Sep 10, 2010
Messages:
7
Sorry not to attach the file.Here it is attached now

File size:
46.5 KB
Views:
35

As Seen On