Help with complex IF formula with DATEAD and DATETIME_FORMAT
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.
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'
)
)
)
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.
Wow! Thanks, i would have never figured that out. I will try this tomorrow and get back to you.
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?
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?
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:
Fecha de registro
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?
