Help

Date Formula Error

Topic Labels: Formulas
1260 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Matt_C
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

3 Replies 3

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

Screenshot 2019-07-30 at 09.50.15.png

JB

Matt_C
5 - Automation Enthusiast
5 - Automation Enthusiast

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
5 - Automation Enthusiast
5 - Automation Enthusiast

Got it!

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

Nested IF! Thank you!