Date Formula Error

[Matt_C]

I’m using the following formula to check whether the data in another column (Call Forward) is before today. If yes, then enter today’s date, if no then enter the Call Forward date.

IF(IS_BEFORE({Call Forward},TODAY()),TODAY(),{Call Forward})

I’m getting an error where the Call Forward field is blank which, I suspect, is caused by the blank (if blank I want today’s date). I’m guessing I need and OR statement to check for blanks but cannot find a suitable formula to check for blanks.

I’ve tried using BLANK() but still getting an error:
IF(OR(BLANK({Call Forward}),IS_BEFORE({Call Forward},TODAY())),TODAY(),{Call Forward})

Grateful for any advice.

[JonathanBowen]

Hi @Matt_C - you can check for the existence of {Call Forward} first. If it is blank, then the rest of the formula will not be evaluated, but if there is a value in it, your original formula will be evaluated:

IF(
  {Call Forward},
  IF(
    IS_BEFORE({Call Forward}, TODAY()),
    TODAY(),
    {Call Forward}
  )
)

JB

[Matt_C]

Thanks, JB. I see how it’s simply a case of using the field, however, I would like to return today’s date if {call forward} is blank. I tried the following but it’s still not working:

IF(OR({Call Forward},IS_BEFORE({Call Forward},TODAY()),TODAY(),{Call Forward})

[Matt_C]

Got it!

IF({Call Forward}, IF(IS_BEFORE({Call Forward},TODAY()),TODAY(),{Call Forward}),TODAY())

Nested IF! Thank you!