Compare two non-static dates with Nested If Statements

[LBinGA]

On my sheet/form, I have two dates to be entered, Eff Date & Ret Date
In another cell, called DIscount, I want to return a value based on how old Ret Date is when compared to Eff Date.

For example:
If Ret Date is up to 1 Year older than Effec Date, return 1.19 in the Discount Field
If Ret Date is between 1 Year (and 1 day) and 2 Years older than Effec Date, return 1.28 in the Discount Field
If Ret Date is between 2 Years (and 1 day) and 3 Years Older than Effec Date, return 1.33 in the Discount Field
If Ret Date is more than 3 Years Older than Effec Date, return 1.36 in the Discount Field

It's twisting my brain on how to write this formula. Can anyone provide a bit of help?

Thanks in advance,
LBinGA

 

[jasonb75]

Try

=CHOOSE(MIN(DATEDIF(Eff Date,Ret Date,"Y"),4),1.19,1.28,1.33,1.36)

If it doesn't work, try swapping Eff Date and Ret Date in the formula.

 

[etaf]

this should do it, just not sure if you want the 1 day

=IF(A1<DATE(YEAR(B1)-3,MONTH(B1),DAY(B1)),1.36,IF(A1<DATE(YEAR(B1)-2,MONTH(B1),DAY(B1)),1.33,IF(A1<DATE(YEAR(B1)-1,MONTH(B1),DAY(B1)),1.28,IF(A1<DATE(YEAR(B1),MONTH(B1),DAY(B1)),1.19,""))))

 

[LBinGA]

this should do it, just not sure if you want the 1 day

=IF(A1<DATE(YEAR(B1)-3,MONTH(B1),DAY(B1)),1.36,IF(A1<DATE(YEAR(B1)-2,MONTH(B1),DAY(B1)),1.33,IF(A1<DATE(YEAR(B1)-1,MONTH(B1),DAY(B1)),1.28,IF(A1<DATE(YEAR(B1),MONTH(B1),DAY(B1)),1.19,""))))

This works! Thank you!

As far as the one day, for example, if someone entered 1/1/20 in the Effec Date and 1/1/19 in the Ret Date, that is exactly one year and should show 1.19, which it does. Brilliant! For the next "level" - Between 1 Year & 2 Years - I was trying to disallow the formula to account for the 1 Year mark twice. In other words, it's either "Up to One Year" OR it's Between 1-2 Years. Hope that makes sense. It seems to be functioning exactly as it needs to, choosing the lower discount, which I think it fine.

I'll find out from the guy I'm developing this for.

Thanks again...much appreciated!

LBinGA

 

[LBinGA]

Try

=CHOOSE(MIN(DATEDIF(Eff Date,Ret Date,"Y"),4),1.19,1.28,1.33,1.36)

If it doesn't work, try swapping Eff Date and Ret Date in the formula.

Thank you for the reply. I was unable to get this to work, even when swapping the Effec Date & Ret Date.

LBinGA

 

[etaf]

you are welcome

 

[jasonb75]

I realise that I made an error in the formula, it should have been

=CHOOSE(MIN(DATEDIF(Eff Date,Ret Date,"Y"),3)+1,1.19,1.28,1.33,1.36)

or, using cell references instead of date descriptions,

=CHOOSE(MIN(DATEDIF(A1,B1,"Y"),4),1.19,1.28,1.33,1.36)

where A1 contains the earliest of the dates.

 

[LBinGA]

Thank you. I could not get the cell references formula to work, however the first formula worked like a charm if I changed the position of the Eff Date & Ret Date to B1 (Ret Date) & A1 (Eff Date), like so:

=CHOOSE(MIN(DATEDIF(B1,A1,"Y"),3)+1,1.19,1.28,1.33,1.36)

Thank you!

LBinGA