I need an "if condition" that says if a certain "date" is less then one year from September 30, 2011 display 0.00, if its more then 1 year but less then 2yrs display .05, if its more then 2yrs but less then 3yrs display .10, if its more then 3yrs but less then 4yrs display .15, if its more then 4 years display .20

Here's one way to do it. Your dates would be in B1 and C1. =IF(DAYS360(B1,C1)<365,0,IF(DAYS360(B1,C1)>365,IF(DAYS360(B1,C1)<730,0.05,IF(DAYS360(B1,C1)>730,IF(DAYS360(B1,C1)<1095,0.1,IF(DAYS360(B1,C1)>1095,IF(DAYS360(B1,C1)<1460,0.15,IF(DAYS360(B1,C1)>1460,0.2,""))))))))

You could also do it with and() like this:

=IF(DAYS360(B1,C1)<365,0,IF(AND(DAYS360(B1,C1)>365,DAYS360(B1,C1)<730),0.05,IF(AND(DAYS360(B1,C1)>730,DAYS360(B1,C1)<1095),0.1,IF(AND(DAYS360(B1,C1)>1095,DAYS360(B1,C1)<1460),0.15,IF(DAYS360(B1,C1)>1460,0.2,"")))))

If I am not mistaken, the DAYS360 function considers every month to have 30 days and may not return the correct number of days between 2 dates. Using March 1, 2011 as a Start Date and September 22, 2011 as End Date, DATEDIF returns 205 while DAYS360 returns 201.

9/22/2011 - 3/1/2011 returns 205, so I am assuming that DATEDIF will return the value you are looking for.

See this site for info on how to use the DATEDIF function in Excel:

http://www.cpearson.com/excel/dated...

Wrap the function inside a Nested IF and you should be good to go.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll