Error In my IF formula

#1

Hello

I’ve four columns in a single one table,

  • date de l’échange
  • date de relance
  • date manuelle prévue
  • relance

I would find a formula who fill my “date de relance” field with :

  • if “relance” is = “pas d’action” , fields in “date de relance” will be filled by “ok”
  • if “date manuelle prévue” is not empty, fields in “date de relance” must returns “date manuelle prévue”, with format ‘D/M/YYYY’
  • if not one of the 2 previous, fields in “date de relance” must returns “date de l’échange + 12 days” with format ‘D/M/YYYY’.

I’ve tried this formula :
IF(relance=“pas d’action”,“ok”,IF({date manuelle prévue}!=BLANK(),DATETIME_FORMAT({date manuelle prévue},‘D/M/YYYY’),
DATEADD({date de l’échange},8,‘days’)))

But i’ve a #Error !

“ok” is well returned , it’s working
“date manuelle prévue”, is well returned with the good format ‘D/M/YYYY’

but for adding day to “date de l’échange”, even if field “date de l’échange” is filled or not returns #Error !

I will be very grateful if some of you could help me :slight_smile:

thanks lot
and enjoy your week end

amélie

0 Likes

#2

I was able to fix part of the error by by removing !=BLANK() from your second IF function. When checking to see if a field is not empty, it’s preferable (and more reliable) to use the shortcut format:

IF({date manuelle prévue}, ...)

…which returns True if {date manuelle prévue} contains anything.

Doing that got rid of the error, but left the resulting date looking like this:

2019-04-09T00:00:00.000Z

I then wrapped a similar DATETIME_FORMAT around the DATEADD, which gave me this:

9/4/2019

Here’s the full formula:

IF(
    relance="pas d’action"
    ,"ok"
    ,IF(
        {date manuelle prévue},
        DATETIME_FORMAT(
            {date manuelle prévue},
            'D/M/YYYY'
        ),
        DATETIME_FORMAT(
            DATEADD(
                {date de l’échange},8,'days'
            ),
            'D/M/YYYY'
        )
    )
)

BTW, you said in your comments that you wanted to add 12 days, but had 8 in the formula, so I left it at 8.

0 Likes