What's a formula that can do this?

[bcurrey]

I'm trying to build a basic demand forecast file. On the tab, i have suppler lead time. Then, I want to place be able to enter a qty between columns D-L. This would be the month that I place an order.

In columns N-V, a formula would look at the month of the order, add the lead time, and the put the estimated arrival date of the product.

Example 1: In Apr, I'll order 500 units of item A. If I add 95 days to 4/1/24, that puts me at 7/6, so I need the 500 units to appear in cell Q6.

Is this possible? I've tried a few approaches, and can't get it to work. Thanks!

 

[Asbestos_Jen]

How do you know what the lead time is for each item? Is the lead time in calendar days or business days?

 

[bcurrey]

How do you know what the lead time is for each item? Is the lead time in calendar days or business days?

I'll populate the lead time manually in those cells if it changes. They don't change too often. They are in calendar days