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

Padthelad

Board Regular
Joined
May 13, 2016
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi Pad
I "think" I have a solution for you!
Using IF statements:
Code:
=IF(I1<R$1,S$1,IF(I1><r$1,s$1,if(i4>Q$4,S$4,IF(I1>Q$3,S$3,IF(I1<R$2,S$2))))[CODE]<r$2,s$2))))[ code]

where:
col B is the start date
cols C to F have been simply copied from your table (sum?)
col G is today's's date
Col H is =G1-B1 (gives the number of days)
Col I is =H1/365 (gives the years a a decimal)
Col J is the formula above
Cell Q1 is blank, Q2 is 2, Q3 is 3 and Q4 is 4
Cell R1 is 2, R2 is 3, R3 is 4 and R4 is blank
Cell S1 is 100%, S2 is 75%, S3 is 50% and S4 is 0%
Cell T1 is 0%, T2 is 25%, T3 is 75% and T4 is 100%

Changing the values in Columns Q to T can be amended and the result will change.
The order of the IF statements is key.

Hope I have understood your issue correctly!
Small Paul.</r$2,s$2))))[></r$1,s$1,if(i4>
 
Last edited:
Upvote 0
Code:
=IF(I1<R$1,S$1,IF(I1>Q$4,S$4,IF(I1>Q$3,S$3,IF(I1<R$2,S$2))))
It seems only part of the statement copied
 
Upvote 0
Another option:

=LOOKUP(DATEDIF(B6,TODAY(),"y"),{0,2,3,4,...},{"100%","75%","50%","25%",...})

Note the highlighted ... are not part of the formula. I added them to indicate if you had more ranges.
 
Upvote 0
Small Paul,

If you are using > or < in your formulas, surround them with spaces on either side. Otherwise, they get treated as HTML tags, and your formulas get cut off like that.
 
Upvote 0
Hi Pad
I "think" I have a solution for you!
Using IF statements:
Code:
=IF(I1<r$1,s$1,if(i1><r$1,s$1,if(i4>Q$4,S$4,IF(I1>Q$3,S$3,IF(I1<r$2,s$2))))[code]<r$2,s$2))))[ code]

where:
col B is the start date
cols C to F have been simply copied from your table (sum?)
col G is today's's date
Col H is =G1-B1 (gives the number of days)
Col I is =H1/365 (gives the years a a decimal)
Col J is the formula above
Cell Q1 is blank, Q2 is 2, Q3 is 3 and Q4 is 4
Cell R1 is 2, R2 is 3, R3 is 4 and R4 is blank
Cell S1 is 100%, S2 is 75%, S3 is 50% and S4 is 0%
Cell T1 is 0%, T2 is 25%, T3 is 75% and T4 is 100%

Changing the values in Columns Q to T can be amended and the result will change.
The order of the IF statements is key.

Hope I have understood your issue correctly!
Small Paul.</r$2,s$2))))[code]<r$2,s$2))))[></r$1,s$1,if(i4>[/QUOTE]

Thanks Small Paul.

You method worked, but the method below is easier for my team to understand.

Thank you!
</r$1,s$1,if(i1>
 
Upvote 0

Excel 2010
BCDEFGHIJ
62-Jan-1814-Sep-18100%
72-Jan-1814-Sep-18100%100%
82-Jan-1614-Sep-1875%75%
9
10
1114-Sep-1614-Sep-18100%
1213-Sep-1614-Sep-1875%
132-Jan-1514-Sep-1850%
1402-01-1414-Sep-180%
15
7c
Cell Formulas
RangeFormula
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)))
 
Last edited:
Upvote 0
Another option:

=LOOKUP(DATEDIF(B6,TODAY(),"y"),{0,2,3,4,...},{"100%","75%","50%","25%",...})

Note the highlighted ... are not part of the formula. I added them to indicate if you had more ranges.

Thanks Dreid, this worked a treat!
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top