IF cell is 0-2 years then 100% IF 2-3 years then 75% and so on....

[Padthelad]

Hi,

I am having trouble working the logic for the below scenario:-

I am aiming to achieve a workbook that will automatically tell me what deduction to make if time between two dates is under 2 years (100%), 2-3 years (75%), 3-4 years (50%) and over 4 years (0%).

I have a workbook with a list of dates in column B and column G. Column H has the difference in years, months and days. Column H has the following code:-

=DATEDIF(B7,G7,"y")&" years "&DATEDIF(B7,G7,"ym")&" months "&DATEDIF(B7,G7,"md")&" days"

I am trying to have column I auto populate with the correct deduction depending on the difference between the two dates in column B and G. Table is as follows:-

	B	C	D	E	F	G	H	I
7	02/01/2018	ITEM 1	105.00	21.00	126.0	14/09/2018	0 years 8 months 12 days	100%

<tbody>
</tbody>

The formula I currently have in I1 is:-

=IF(DATEDIF(B6,TODAY(),"y")<=2,"100%",$T$10)

This gives me the desired result, but it does not change if the dates are changed in column B and G.

I have the following table where the deduction amounts are stored. Ideally I would like the formula to take it's information from here so that changes to deductions can be made in the future.

	P	Q	R	S	T
10	Up to 2 Years		2	100%	No Deduction
11	2-3 years	2	3	75%
12	3-4 years	3	4	50%
13	Over 4 years			0%

<tbody>
</tbody>

I hope this all makes sense. I think I may be using the wrong formulas for this problem.

Any help or advice is very much appreciated.

Thanks,

Pad

 

[dreid1011]

You're welcome, and thanks for the feedback.

 

[Padthelad]

Excel 2010

	B	C	D	E	F	G	H	I	J
6	2-Jan-18					14-Sep-18		100%
7	2-Jan-18					14-Sep-18		100%	100%
8	2-Jan-16					14-Sep-18		75%	75%
9
10
11	14-Sep-16					14-Sep-18			100%
12	13-Sep-16					14-Sep-18			75%
13	2-Jan-15					14-Sep-18			50%
14	02-01-14					14-Sep-18			0%
15

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

7c​

Worksheet Formulas Cell Formula I6 =IF(DATEDIF(B6,TODAY(),"y")<=2,1,$S$9) I7 =LOOKUP(DATEDIF(B7,TODAY(),"y"),{0,1;2,0.75;3,0.5;4,0}) J7 =IF((G7-B7)<731,1,IF((G7-B7)<1096,0.75,IF((G7-B7)<=1461,0.5,0))) J11 =IF((G11-B11)<731,1,IF((G11-B11)<1096,0.75,IF((G11-B11)<=1461,0.5,0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks Dave. Also worked well and is simple to follow.

Thanks for your help.