Help with EDATE, calculating estimated forward date

[mhelman]

Hey Guys,

Been reading the forum for a long time and it's been a great resource for me over the years. I'm having some trouble with the EDATE function and was looking for some help.

In Column B I have a bunch of dates of when the product was sold.

In Column F I have the term of that sale, in months (1 to 36 months).

In Column H I have the date the person should be running low on the product and need more (this is the cell I need help with).

In Column L I have the brand of the product sold.

In a separate sheet I have an Assumptions page, that has each brand we sell, and the corresponding time (based on the brand) the person should need more, expressed in a percentage. Column A is the Brand, and Column B is the percentage. For example, if we sell them Brand A, they will need more of Brand A 50% through the term of the contract. So if it's a 12mo term (Column F), they will need more of Brand A in 6 months (12mo x 50%).

Right now the current formula I'm using for Column H basically assumes that everyone needs more of the product 50% through the term. The current formula is =EDATE(B2,F2/2). Right now this formula is sorely lacking. For example, if we sold a customer a product on 11/6/2014, and the term of the sale was 3 months, with my existing EDATE formula, it says the person is eligible for more of the product on 12/6/2014. Clearly that is not 1.5 months from 11/6/2014.

Overall, this existing formula is crappy, and I want a more accurate date based on the Assumptions sheet percentages from the date of the sale. I'm pretty sure I need an IF VLOOKUP formula, looking up the Brand being sold in Column L, pulling the percentage from Column B in the Assumptions sheet for that Brand, and multiplying that percentage times the term in Column F, and then adding that to Column B (the date of the sale). The problem is I don't know how to incorporate the EDATE formula into all of this. Maybe there is a better way to do it without using the EDATE formula. And if I need to, I'm happy to convert the months into days if that's going to make the formula more accurate.

I hope this makes sense. I know how to do IF VLOOKUPS, but I just need help with the date portion of all of this.

Thanks guys!

 

[skywriter]

I'm trying to picture your data in my head so if this isn't right I'd need to see an example, but why couldn't you just use 30 days as the basis for a month and do something like the following:

Excel 2010
	A	B	C	D
1	Date Sold	Term(Months)	Percentage	More Needed
2	11/11/2014	3	50%	12/26/2014
Sheet8

Cell Formulas
Range	Formula
D2	=A2+30*B2*C2

 

[ukmikeb]

Hi

EDATE only works for whole months!

Try :-

=$A2+INT((EDATE($A2,B2)-$A2)*$C2)

which will give you a more accurate figure.

hth

 

[mhelman]

I'm trying to picture your data in my head so if this isn't right I'd need to see an example, but why couldn't you just use 30 days as the basis for a month and do something like the following:

Excel 2010
	A	B	C	D
1	Date Sold	Term(Months)	Percentage	More Needed
2	11/11/2014	3	50%	12/26/2014
Sheet8

Cell Formulas
Range	Formula
D2	=A2+30*B2*C2

THANK YOU!!!

This worked great. I just inserted a VLOOKUP for the percentage (Column C in your example) to pull in the info I needed for the specific brand from my Assumptions sheet.

This board is great. Thanks again for your help

 

[skywriter]

THANK YOU!!!

This worked great. I just inserted a VLOOKUP for the percentage (Column C in your example) to pull in the info I needed for the specific brand from my Assumptions sheet.

This board is great. Thanks again for your help

Great, thanks for the feedback. My pleasure.