Excel

[LearningFurther]

I want to sum x amount of months (no more than 12) usually 4- months at a time, subtract another cell of inventory levels, then use the current date to return a date of how long the inventory will last. 

Either a return of Aug 8th, 2022....November 9th, 2023, etc.

or 

1 month and a half, 4 months and 3/4s, etc.

I am currently using this formula, 

=EDATE($A$2,12)-(SUM(M5:P5)-C5)

and I seem to be getting dates back to 2007 or forward till 2024, I can not figure out what is wrong with the formula

a$2$= current date, (12) is months

Sum is totalling 4 months worth of pending builds to be produced and (c5) is the current inventory level. 

[#2]

@LearningFurther 

EDATE($A$2,12) is the date 1 year after today.

(SUM(M5:P5)-C5) subtracts the current inventory level from the sum of pending builds.

Does it make sense to subtract that directly from a date?

[#3]

@Hans Vogelaar

Do you know of a better approach?
What I am after is having the ability for an automated return. I want to be able to take 4 months' worth of builds subtract out my current higher level of the build and then in return figure out that it will last only 1 month, 3 months and a half, 4 months and 3/4's, etc.

[#4]

@LearningFurther 

You'll have to provide more information. How do builds and months and inventory relate to each other?

[#5]

@HansVogelaar
Let's say,
Month 1 (Aug) I have an overstock of 180PCS that overstock rolls over into Sept PB of 220 therefore I only need to build a remaining balance of 40. Oct I want to build another 220, Nov I want to build another 220, My current inventory level is 0 therefore I need to build a minimum of 479. Therefore I have roughly 1-Month and 1/2 worth of inventory.

This is the return I am looking for. either I will run out by Aug 29th, 2022, and/or I have 1-Month and 1/2 worth of inventory.

Then looking over the bigger picture I would do this for every inventory level and then organize based upon the date I am running out first, and that starts the new production schedule.

Does that help?

[#6]

@LearningFurther 

I'll have to leave this to someone else. I don't understand this at all, sorry.

[#7]

@Hans Vogelaar I appreciate you trying to help!

Thank you for your time!

[#8]

@Hans Vogelaar

I thought about this way of presenting the issue

Looking at open sales orders:

If I have a column that is labeled inventory part number "A" (Example: But can range from row 1 to 30 or 1-5000)

If I have a column that is labeled quantity per purchase order "B" (Example: But can range from row 1 to 30 or 1-5000)

If I have a column that is labeled requested date "C" (Example: But can range from row 1 to 30 or 1-5000)

Then I have a cell of inventory levels "D" and "E" (One cell for each level)

(An assembly and Subassembly)



Can I have Excel look at the Part number "A", Talley up the QTY "B", and then subtract "-" stock inventory levels "D+E", while looking at the requested date and have Excel tell me by 8/16/2022 You will be "X-amount" short for that order?