Inventory run down based on date in another cell

[sandwichgirl]

Hi All

I am using a formula that calculates the run out date of stock based on a weekly usage and an opening stock figure - it then provides me with the date of the week the stock runs out.

I would like to have an alternative version that looks at a date when new stock comes in and calculates from there instead.

The current formula is:

INDEX($Z$4:$AS$4,MATCH(TRUE,SUBTOTAL(9,OFFSET(Z5:AS5,,,,COLUMN($Z$4:$AS$4)-COLUMN(Z5)+1))>+O5),0))

Z4:AS4 are the weekly dates, Z5:AS5 is the weekly usage
O5 is the stock quantity
and N5 is the date this new stock is available
I'd like a run out date calculated based on starting in N5 - how do I insert a 'start' date in this formula?

My specific query is, if the stock run out date is greater than the new stock date, then use the stock run out date, but if the new stock date is greater than stock run out, use the new stock date to calculate a new stock run out date.

Hope this makes sense and apologies if not.

thank you