Plan a monthly production in specific week based on expected stock out date

Hello,

I'm looking for a formula to place a monthly production into a specific week 7-10 days before the predicted stock out date.
The opening stock compared to next months forecast will predict what day of the month the stock covers to.
The monthly production is set up by formula to give a desired month end stock and also factors in a min run.
Currently I manually place this in a specific week but I want to automate it into a week so production takes place at least the week prior to stocking out.

I've been able to do this by asking if the opening stock is less than average week stock x 1.5 then the monthly production goes into that week, else a blank...... but the problem is the following week pulls in the same result and I've not been able to nest if statements properly to put in a blank a week later if production has been filled the prev week. Hope that makes sense!

Extras I'd like later would be to maybe split a run over two weeks if the monthly production exceeds a max run but that's for later maybe!

Struggling with the attachment so hopefully it goes up with this!

This formula places the values in the same cells as they were previously place (manually): =IF(ISTEXT(K3),"",IF(AND(K3>=DATE(YEAR(K3),MONTH(K3),$F4)-10,K3<=DATE(YEAR(K3),MONTH(K3),$F4)-7),$J4,""))
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.