# Compare two non-static dates with Nested If Statements

#### LBinGA

##### Board Regular
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?

LBinGA

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### jasonb75

##### Well-known Member
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

##### Well-known Member
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

##### Board Regular
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

##### Board Regular

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

you are welcome

#### jasonb75

##### Well-known Member
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

##### Board Regular
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,021
Messages
5,834,992
Members
430,331
Latest member
Syed Yasir Hannan

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

### Which adblocker are you using?

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

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