Skip to main content

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 🙂


thanks lot

and enjoy your week end


amélie

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.

Reply


Terms & Conditions