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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,485
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
4,137
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
11,485
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,358
Messages
5,571,708
Members
412,413
Latest member
dvprajapati
Top