Calculate the shelf-life of semi-finished goods when it becomes finished goods

[Mayqueul]

Dear Excel community,
Could you help me solve below problem: I have 1 semi-finished product (line 2) which is transformed into finished goods (line 4).
I would like to calculate the time spent (line 7) between when the semi-finished product is released and when it is transformed.

 

[vletm]

Mayqueul​

How did You get those line 7 values?

 

[Mayqueul]

Dear Vietm,

I use the following logic: the production in July (F4) is using the semi-FG released in April (C2) because I'm using FEFO (First Expired First Out).
So I need a formula which looks backward and flag the month (X) when the following condition is met: sum ( semi-FG released + stock available) of previous month minus sum ( FG to be produced from M0 until M-X) is negative, with X = the month when the semi-FG is released.

I have updated my excel file with line 8 to show the condition.

Example 1:
- production in July (orange F4) is made from semi-FG released in April (orange C2)
- because April is the first month (backward looking) when stock F3 turns negative when sum (B2:B3) - sum ($F4:C4) < 0
- the result I'm looking for is July minus April = 3 months
Example 2:
- production in Oct (green I4) is made from semi-FG released in July (green F2)
- because July is the first month when stock I3 turns negative when sum (E2:E3) - sum ($I4:F4) < 0
- the result I'm looking for is Oct minus July = 3 months
Example 3:
- production in Dec (green K4) is made from semi-FG released in July (green F2)
- because July is the first month when stock K3 turns negative when sum (E2:E3) - sum ($K4:F4) < 0
- the result I'm looking for is Dec minus July = 5 months
Example 4:
- production in Feb (yellow M4) is made from semi-FG released in Aug (yellow G2)
- because Aug is the first month when stock M3 turns negative when sum (F2:F3) - sum ($M4:G4)
- the result I'm looking for is Feb minus Aug = 6 months

I hope my explations can help you solve the issue I have been struggling with since few days

 

[vletm]

Mayqueul​

Could You verify Your looking for results with Examples 3 & 4?

 

[Mayqueul]

Thank you Vietm,
I revised the formula in line 10.
The logic breakdown is now correct.
I just need a formula to sum it up

 

[vletm]

Mayqueul​

... You just need something ...
I've an image the My Excel ... misses features to build formula solution ( I cannot verify it ).
I could offer something else later.
Or
You should wait that someone else would do formula solution for You.
Now, it should be possible.

 

[vletm]

Mayqueul​

Seems that other are still thinking ...
As I wrote ... I could offer something else ... like this

Your data have to be as in Your layout (rows 2,3,4).
I added to row 20 my sample solution.
You can paste that sample function (one-by-one) to any row below row 4.

 

[Mayqueul]

Hello Vietm,
Thank you for your proposal.
I opened your file, but the cells in line 20 shows only "=@do_It()" ?
I tried to enable macro but it didn't change anything.
Is there anything I should do ?

 

[vletm]

Mayqueul​

Okay - macros have to be enabled.
Row 20 ( C20:R20 ) should show Your expected results - same as Your row seven.
Your Is there anything I should do ?
I could ask same kind of question: When should Your row seven results change?
You could eg try to
> select cell C20
> press Ctrl+C
> select cell C18
> press Ctrl+V
>>> You should see same results in cells C18 and C20
With that function, You should able to
Calculate the shelf-life of semi-finished goods when it becomes finished goods
as You've asked ... with same layout as You've given in Your file.

 

[Mayqueul]

Thank you very much Vietm, I backtested your macro and it works great !

 

[vletm]

It's sensitive with layout ... very sensitive.