+ Reply to Thread
Results 1 to 5 of 5

Date calculation based on data in a spreadsheet to identify start date for material

  1. #1
    Registered User
    Join Date
    06-01-2012
    Location
    Detroit, MI
    MS-Off Ver
    Office365
    Posts
    24

    Date calculation based on data in a spreadsheet to identify start date for material

    I have a spreadsheet that has a row range showing inventory depletion. The headers to these rows have a date. I also have a cell with a number in that represents how many days it takes to manufacture a lot of material. I need to identify when inventory turns negative in the first range, then look at the date in the header of that column, then calculate the start date going backwards from that date based on the number in the cell I have identified is the manufacturing time. No idea how to write this?
    Last edited by ikench; 06-01-2012 at 01:32 PM. Reason: Not following forum rules

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: MS Excel Formula Problem

    HI Ikench,

    Suggest you to change the thread title to reflect the query being asked ... see the forum rules:-


    http://www.excelforum.com/forum-rule...rum-rules.html


    Regards,
    DILIPandey


    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    06-01-2012
    Location
    Detroit, MI
    MS-Off Ver
    Office365
    Posts
    24

    Re: MS Excel Formula Problem

    Hope this is better, sorry.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Date calculation based on data in a spreadsheet to identify start date for material

    Hi Ikench,

    Thanks for updating the thread title...

    I believe you can use the good mix of the formulas like Offset/ Match / Workday etc.. would suggest you to upload the sample workbook. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    06-01-2012
    Location
    Detroit, MI
    MS-Off Ver
    Office365
    Posts
    24

    Re: Date calculation based on data in a spreadsheet to identify start date for material

    OK, I worked it out, but had to break it down into 3 cells.

    Cell 1 (AA8) was used to calculate the quantity closest to 0. =INDEX(K8:V8,MATCH(MIN(ABS(K8:V8-AA$4)),ABS(K8:V8-AA$4),0))
    Cell 2 (AB8) was used to calculate the date at the top of the column closest to 0. =INDEX($K$7:$V$7,MATCH(AA8,INDEX($K$8:$V$81,MATCH(I8,I$8:I$81,0),0),0))
    Cell 3 (AC8) was used to count back days from AB8 based on a given lead time. =SUM(AB8-D$2)

    Probably an easier way but this is how I got it, and it works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1