Skip to main content

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

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?

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:

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

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


PS: Indented should look something like this:

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


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:

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!

Terms & Conditions