Skip to main content

Help with complex IF formula with DATEAD and DATETIME_FORMAT

  • October 25, 2018
  • 6 replies
  • 68 views
B

Hi, im using Airtable to calculate invoice dated from when a client signs up depending on payment method and type. I have a start date, and i have a last invoice date and next invoice date. And im using a formula now to calculate next invoice date based on last invoice date. But if the last invoice date is empty i get an error. What i want to happen is that if there is no last invoice date, it should instead be start date.

Here is my current working formula:

DATETIME_FORMAT(DATEADD({Last Invoice Date},IF({Payment Method}="Quarter",90,IF({Payment Method}="Partner",360,30)),'day'),'YYYY-MM-DD')

And as i said, if {Last Invoice Date} is empty i get an #error. My manual solution now is to just enter the start date manually in the last invoice field.

What i want to do is to base the first invoice date on the start date, if the last invoice field is empty. and i tried this.

IF
({Last Invoice Date} = BLANK(
    (DATETIME_FORMAT
        (DATEADD
            ({Start},30)
        ),'day'
    ),'YYYY-MM-DD'),
    (DATETIME_FORMAT
        (DATEADD
            ({Last Invoice Date},
                IF
                ({Payment Method}="Quarter",90,
                    IF
                    ({Payment Method}="Partner",360,30
                    )
                ),'day'
            ),'YYYY-MM-DD'
        )
    )
)

    6 replies

    J
    Forum|alt.badge.img+18
    DATETIME_FORMAT(
   DATEADD(
      IF(
         {Last Invoice Date},
         {Last Invoice Date},
         {Start}
      ),
      SWITCH(
         {Payment Method},
         "Quarter", 90,
         "Partner", 360,
         30
      ),
      'day'
   ),
   'YYYY-MM-DD'
)

    That should do it. Since what you want is to check for the presence of any value in {Last Invoice Date}, and change out only its value for another if it fails, all the rest being the same, you want to keep the logic that checks this contained to the smallest scope possible - thus, we make that check inside the DATEADD() function. The IF() statement I used there just checks for a “Truthy” value in {Last Invoice Date} - a “Truthy” value, in this case, is any non-blank value, which will have the effect of evaluating to that very value, the value in {Last Invoice Date}. But if that field is blank, it evaluates as false, and moves on to the false condition, which is the value in {Start}.

    I also changed your nested conditional for {Payment Method} to Airtable’s new SWITCH() function, which is a much cleaner and more concise way to express the same thing:

    Let me know if it doesn’t work – I didn’t test it out at all, so it’s always possible I made a mistake.

      B
      • BlackO
      • Author
      • New Participant
      • October 25, 2018

      Wow! Thanks, i would have never figured that out. I will try this tomorrow and get back to you.

        B
        • BlackO
        • Author
        • New Participant
        • October 26, 2018
        DATETIME_FORMAT(
   DATEADD(
      IF(
         {Last Invoice Date},
         {Last Invoice Date},
         {Start}
      ),
      SWITCH(
         {Payment Method},
         "Quarter", 90,
         "Partner", 360,
         30
      ),
      'day'
   ),
   'YYYY-MM-DD'
)

        That should do it. Since what you want is to check for the presence of any value in {Last Invoice Date}, and change out only its value for another if it fails, all the rest being the same, you want to keep the logic that checks this contained to the smallest scope possible - thus, we make that check inside the DATEADD() function. The IF() statement I used there just checks for a “Truthy” value in {Last Invoice Date} - a “Truthy” value, in this case, is any non-blank value, which will have the effect of evaluating to that very value, the value in {Last Invoice Date}. But if that field is blank, it evaluates as false, and moves on to the false condition, which is the value in {Start}.

        I also changed your nested conditional for {Payment Method} to Airtable’s new SWITCH() function, which is a much cleaner and more concise way to express the same thing:

        Let me know if it doesn’t work – I didn’t test it out at all, so it’s always possible I made a mistake.


        I just tested and it works perfectly! BUT I realize I made an error in my thinking. If the Last Invoice Date is empty, and Start date is used, there should be no DATEADD (or 0) so its same invoice date as the start date.
        That’s why I was doing the convoluted nested IF statement.

        IF {Last Invoice Date} = empty use {Start} date else use {Last Invoice Date} plush swhitch method?

        IF({Last Invoice Date} = BLANK(),
    (DATETIME_FORMAT(Start, 'YYYY-MM-DD'),
        (DATETIME_FORMAT
            (DATEADD
                ({Last Invoice Date},
                  SWITCH(
                     {Payment Method},
                     "Quarter", 90,
                     "Partner", 360,
                     30
                     ),
                     'day'
                ),
                'YYYY-MM-DD'
            )
        )
    )
)

        And of course, that didn’t work.

        B
        • BlackO
        • Author
        • New Participant
        • October 26, 2018

        I just tested and it works perfectly! BUT I realize I made an error in my thinking. If the Last Invoice Date is empty, and Start date is used, there should be no DATEADD (or 0) so its same invoice date as the start date.
        That’s why I was doing the convoluted nested IF statement.

        IF {Last Invoice Date} = empty use {Start} date else use {Last Invoice Date} plush swhitch method?

        IF({Last Invoice Date} = BLANK(),
    (DATETIME_FORMAT(Start, 'YYYY-MM-DD'),
        (DATETIME_FORMAT
            (DATEADD
                ({Last Invoice Date},
                  SWITCH(
                     {Payment Method},
                     "Quarter", 90,
                     "Partner", 360,
                     30
                     ),
                     'day'
                ),
                'YYYY-MM-DD'
            )
        )
    )
)

        And of course, that didn’t work.


        I finally managed to figure it out:

        IF(
    {Last Invoice Date} = BLANK(),DATETIME_FORMAT(Start, 'YYYY-MM-DD'),(
        DATETIME_FORMAT(
            DATEADD(
                {Last Invoice Date},
                SWITCH(
                    {Payment Method},
                    "Quarter", 90,
                    "Partner", 360,
                    30
                ),
                'day'
            ),
            'YYYY-MM-DD'
        )
    )
)
          P
          Forum|alt.badge.img+3

          Love how it got simpler as you went! Also loved learning a better way around the nested if statements… sweet.

            K
            Forum|alt.badge.img+4
            DATETIME_FORMAT(
   DATEADD(
      IF(
         {Last Invoice Date},
         {Last Invoice Date},
         {Start}
      ),
      SWITCH(
         {Payment Method},
         "Quarter", 90,
         "Partner", 360,
         30
      ),
      'day'
   ),
   'YYYY-MM-DD'
)

            That should do it. Since what you want is to check for the presence of any value in {Last Invoice Date}, and change out only its value for another if it fails, all the rest being the same, you want to keep the logic that checks this contained to the smallest scope possible - thus, we make that check inside the DATEADD() function. The IF() statement I used there just checks for a “Truthy” value in {Last Invoice Date} - a “Truthy” value, in this case, is any non-blank value, which will have the effect of evaluating to that very value, the value in {Last Invoice Date}. But if that field is blank, it evaluates as false, and moves on to the false condition, which is the value in {Start}.

            I also changed your nested conditional for {Payment Method} to Airtable’s new SWITCH() function, which is a much cleaner and more concise way to express the same thing:

            Let me know if it doesn’t work – I didn’t test it out at all, so it’s always possible I made a mistake.


            Hola chicos! Yo tengo un tema similar, Tengo los siguientes factores:

            1. Fecha de registro
            2. Días de plazo

            Quiero saber 2 cosas:
            ¿Cuántos días faltan para que me paguen a partir de la fecha de registro si el plazo es a 90 días?
            Una vez calculado lo anterior, ¿Cual es la fecha de pago?

            Me podrían ayudar? Espero haberme explicado

            Saludos!
            KM

            Terms & ConditionsAccessibility statement