1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

How to calculate the days ..Pl guide me

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

Thread Status:
Not open for further replies.
Advertisement
  1. Gupta

    Gupta Thread Starter

    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:

  2. Gupta

    Gupta Thread Starter

    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

    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?
     
  4. Gupta

    Gupta Thread Starter

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

    Gupta Thread Starter

    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

    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

    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

    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:

  9. Gupta

    Gupta Thread Starter

    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:p").Column Then
    Cells(.Row, "Q").Value = Int(Now)
    End If
    End With
    Next Cell
    End Sub
     
  10. Gupta

    Gupta Thread Starter

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

    Attached Files:

  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/950094

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice