Delivery time and production time

[PBrabander]

Hi fellow Excellers, I am currently working on a production list. As an example, I have made the following delivery overview of a type of chair.

Week	12	13	14	15	16	17	18	19	20
Amount	18	23	47	87	76	54	43	12	9

A number of production steps are required to make this chair. This takes a few weeks. See below the time required per step.

Wood processing	2,5 weeks
Detail and paint	1,5 weeks

Because the production weeks are complete but the steps consist of half weeks, I run into a challenge. Can someone step in and provide a formula that makes it possible to subtract the production time from the delivery moment? Thanks in advance!

[#2]

@PBrabander 

Maybe assume that you need a 4-week lead to enter production but split the amount entering final steps between 1- and 2-weeks lead depending on what one assumes the delivery day to be.

EnterProduction
= XLOOKUP(Week+4, Week, Amount, 0)

FinalProduction
= ROUND(
    ( 3*XLOOKUP(Week+2, Week, Amount, 0) 
    + 2*XLOOKUP(Week+1, Week, Amount, 0)  
    ) / 5,
  0 )