Help

Nested IF Formula Help

1605 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Robert_Hudman
6 - Interface Innovator
6 - Interface Innovator

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”)

image

Any suggestions would be great. Thank you in advance.

Robert

5 Replies 5
Robert_Hudman
6 - Interface Innovator
6 - Interface Innovator

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?

Hi Robert,

Please check if this is what you intend to do:

image.png

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

PS: Indented should look something like this:

image.png

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:

Thanks Luiggi
Works perfect

You have taught me a great deal!