Run out Date Formula

Hi

Base on SnowMan workbook

=IFERROR(LET(p,F2:AS2,INDEX(p,,SUM((SCAN(0,p*ISEVEN(COLUMN(p)),LAMBDA(c,x,c+x))<B2)*1)+2)),"")

Jacko320 

Jacko320 

As the IF function can only be nested 64 levels, using that (a "brute force" technique) won't meet your needs. (And it would be as ugly as heck to write and debug if it did work.)

The calculation must iterate over the pairs of quantity and date cells until, say, it finds an Open Order Date cell that does not contain a date. So until recent years, you would have to either add many helper cells for the calculations, or calculate it in a VBA procedure (or possibly in Office Script, with which I am not familiar).

If your version of Excel supports the LAMBDA function, you can define a LAMBDA function that does a calculation and conditionally (i.e., if run out has not occurred) calls itself with different arguments. (A function that calls itself is called a recursive function.)

I have created the function CheckForRunOutR (the R just reminds me that it is for checking data chronologically to the right). Feel free to use a different name, but remember to change it inside the LAMBDA formula (as well as the formula name definition, and in cell formulas).

=LAMBDA(Remaining,NextQty,NextDt,
    IF(AND(ISNUMBER(NextQty),NextQty>=0),
        IF(Remaining-NextQty<=0, NextDt,
            CheckForRunOutR(Remaining-NextQty,OFFSET(NextQty,0,2),OFFSET(NextDt,0,2))),
        IF(ISNUMBER(NextDt),
            "-bad data in "&CELL("address",NextQty),
            ">"&TEXT(OFFSET(NextDt,0,-2),"mm/dd/yy")
        )
    )
)

All those extra spaces and soft line feeds are just present to make it easier to read and understand.

If there will be no runout with the open orders, I have the function prepend ">" to the last date. Alternatively, you could have it just output "-none-" or another text literal or a date-in-the-far-future literal.

Note that I did not include logic to handle any cases where:

• the Open Order Dates are not in chronological order, or
• the initial quantity is zero or negative, or
• a non-integer quantity was specified where an integer was required (My formula would not know which SKUs require an integer quantity.)

But I did include logic to flag a missing or nonnumeric or negative OO Qty if the OO Date is present. The function permits a zero OO Qty (NextQty>=0, rather than NextQty>0) to allow you to test a what-if-an-order-is-cancelled condition manually with ease.

As in the attached workbook, I have not tested it beyond twenty open orders. But I do not expect problems (other than slow computations) with many more columns of open orders.

Regarding your data design: Is it a significant problem to maintain the data in the layout you showed? I would have put the open order occurrences into their own rows (not their own columns) in a separate worksheet. I believe it would be easy to change the LAMBDA function above to accommodate that alternative.