Formula if between two dates

Platform discussions Special workflows & use cases
1

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

2

@Jean

Try this:

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

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

3 Likes
3

@JCorrell Unfortunately it doesn’t work :frowning:

4

@Jean

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

5

@JCorrell

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

It’s very amazing

6

@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