# Formula if between two dates

Hello,

I would like to create an IF and AND formula which calculates the duration between a date column and the current date and thus defines a rate.

Terms :
1- Date is less than 12 months so 10%
2- Date is between 13 and 24 months then 7%
3-Date is greater than 24 months then 3.5%

I can’t find the error: IF(AND((MONTH({Date})>= 0,(MONTH({Date}) <= 12), 10, IF(AND((MONTH({Date})=> 12.1,(MONTH({Date}) =< 23), 7, IF(AND((MONTH({Date})>= 24,(MONTH({Date}) =< 12), 3.5, 0)))))))))
Can someone help me?
(sorry for my English)
Thanks,
Jeans

@Jean

Try this:

``````IF(YEARFRAC(TODAY(), {Date}) * 12 <= 12, 10%,
IF(YEARFRAC(TODAY(), {Date}) * 12 <= 24, 7%, 3.5%))
``````

Jim - The Monday Man
Column Magic - the magical columns toolbox app

3 Likes

@JCorrell Unfortunately it doesn’t work

@Jean

Do you want help? How exactly is it not working?

@JCorrell

Yes, It shows me an error or a result (0.1).

It’s very amazing

@Jean

0.1 is 10%

The function will return an error if {Date} is blank.

This version will return nothing if {Date} is blank and the values as your given percentages time 100:

``````IF({Date}, IF(YEARFRAC(TODAY(),
IF({Date}, {Date}, "1/1/1")) * 12 <= 12, 10,
IF(YEARFRAC(TODAY(), IF({Date}, {Date}, "1/1/1")) * 12 <= 24, 7, 3.5)),
"")
``````
2 Likes