Nested IF Formula Help

[Robert_Hudman]

Hi There

I am trying to set up a formula to read from a single date field and provide an output text to give me three desired output. As below:

• If the date <= today’s date + 30 days - show as “30 days”
• If the date <= today’s date + 60 days (but not equal to above) - show as “60 days”
• If the date <= today’s date + 90 days (but not equal to above) - show as “90 days”

I understand I will need to use a Nested IF Formula to achieve this…however the problem is, I cannot seem to make even un-nested original formula work to get the first “30 days” result.

I have it working for the base formula :

IF({Conservative Date} <= TODAY(),“yes”,“no”)

(note - I have just used ‘yes’ and ‘no’ as a test as I progress through my knowledge)

However when I am getting errors where I try to make it today’s date + 30 days (instead of just today). I know this sounds simple and basic…but do I use the ‘+’ symbol? SUM() function? Everything I try does not work and just returns ‘Error’ message.

I have been trying things like:

IF({Conservative Date} <= (TODAY() + 30),“yes”,“no”)

Any suggestions would be great. Thank you in advance.

Robert

[Robert_Hudman]

Alright…just figured out there is a DATEADD function! :grinning:

Using this it seems to work:

IF({Conservative Date} <= DATEADD(TODAY(), 30, ‘days’),“30 days”,“nested”)

Can anyone give me advise on how to deal with the 60 days and 90 days in the nested marker above?

[Luiggi_Cuozzo]

Robert_Hudman:

IF({Conservative Date} <= DATEADD(TODAY(), 30, ‘days’),“30 days”,“nested”)

Hi Robert,

Please check if this is what you intend to do:

Formula used for “Field 7” is this:

IF(DATETIME_DIFF({Conservative Date},DATEADD(TODAY(),30,‘days’),‘days’)<=0,“30 days”,
IF(DATETIME_DIFF({Conservative Date},DATEADD(TODAY(),60,‘days’),‘days’)<=0,“60 days”,“90 days”
)
)

Hope it helps

Rgds

[Luiggi_Cuozzo]

PS: Indented should look something like this:

[Luiggi_Cuozzo]

And this one also got the same results:

IF(DATETIME_DIFF({Conservative Date},TODAY(),‘days’)<=-30,“30 days”,
IF(DATETIME_DIFF({Conservative Date},TODAY(),‘days’)<=-60,“60 days”,“90 days”))

:smiling_face_with_sunglasses:

[Robert_Hudman]

Thanks Luiggi
Works perfect

You have taught me a great deal!