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

P

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:-

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]02/01/2018
[/TD]
[TD]ITEM 1
[/TD]
[TD]105.00
[/TD]
[TD]21.00
[/TD]
[TD]126.0
[/TD]
[TD]14/09/2018
[/TD]
[TD]0 years 8 months 12 days
[/TD]
[TD]100%
[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Up to 2 Years
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]100%
[/TD]
[TD]No Deduction
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]2-3 years
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]75%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]3-4 years
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]50%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Over 4 years
[/TD]
[TD][/TD]
[TD][/TD]
[TD]0%
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
 
Sort by date Sort by votes

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Dave Patton said:
Excel 2010
BCDEFGHIJ

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: right"]2-Jan-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2-Jan-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]100%[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2-Jan-16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]75%[/TD]
[TD="align: right"]75%[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]14-Sep-16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100%[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]13-Sep-16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]75%[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2-Jan-15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50%[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"] 02-01-14 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0%[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
7c​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I6[/TH]
[TD="align: left"]=IF(DATEDIF(B6,TODAY(),"y")<=2,1,$S$9)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I7[/TH]
[TD="align: left"]=LOOKUP(DATEDIF(B7,TODAY(),"y"),{0,1;2,0.75;3,0.5;4,0})[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J7[/TH]
[TD="align: left"]=IF((G7-B7)<731,1,IF((G7-B7)<1096,0.75,IF((G7-B7)<=1461,0.5,0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J11[/TH]
[TD="align: left"]=IF((G11-B11)<731,1,IF((G11-B11)<1096,0.75,IF((G11-B11)<=1461,0.5,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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

Thanks for your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula to calculate length of the contract using Begin date and end date
Replies
5
Views
511
Micron
M
V
  • Question Question
Cumulative Deposits in Indian Banks and TDS thereon
Replies
1
Views
134
vsuram
V
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
159
ellison
E
E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
210
Tetra201
Tetra201
D
  • Solved
Date lookup in 2 different ranges depending on year
Replies
3
Views
463
etaf
etaf

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

Share this page

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