Compare two non-static dates with Nested If Statements

LBinGA

Board Regular
Joined
Jan 29, 2014
Messages
57
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
 

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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 24, 2012
Messages
5,666
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Jan 29, 2014
Messages
57
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
(y):)(y)
 

LBinGA

Board Regular
Joined
Jan 29, 2014
Messages
57

ADVERTISEMENT

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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 29, 2014
Messages
57
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
 
Master Excel Bundle

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

Threads
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.
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
Top