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