Set one field based on another date

[Andrew_Pulford]

I have two fields - Date Invoiced and Invoice Status (values are Invoiced, Paid, +30 Days, +60 Days, +90 Days).
Can I automatically set the value of Invoice Status based on the time difference between the current day and Date Invoiced?

[W_Vann_Hall]

The following formula in your “Invoice Status” field should give you what you want – with the caveats and notes that follow:

IF(Paid,"paid", IF(ABS(DATETIME_DIFF({Date Invoiced},NOW(),'d'))<30,"Invoiced", IF(AND(ABS(DATETIME_DIFF({Date Invoiced},NOW(),'d'))>29,ABS(DATETIME_DIFF({Date Invoiced},NOW(),'d'))<60),"+30 days", IF(AND(ABS(DATETIME_DIFF({Date Invoiced},NOW(),'d'))>59,ABS(DATETIME_DIFF({Date Invoiced},NOW(),'d'))<89),"+60 days", IF(ABS(DATETIME_DIFF({Date Invoiced},NOW(),'d'))>89,"+90 days","error")))))

I added a checkbox field called ‘paid’ to simplify things; you could instead substitute a check based on amount outstanding or other determination of whether the invoice had been paid. Note this formula treats any invoice 90 or more days old as ‘90+ days’. The ‘error’ status in the final IF() function is simply a sanity check and shouldn’t be displayed under normal operations. The ABS() surrounding the DATETIME_DIFF() formulas can be omitted if you reverse the order of {Date Invoiced} and NOW().

[Christoff]

Simpler formula:

IF(PAID,“Paid”,
IF(DATETIME_DIFF(NOW(),{Date Invoiced},‘d’)<30,“Invoiced”,
IF(DATETIME_DIFF(NOW(),{Date Invoiced},‘d’)<60,"+30 days",
IF(DATETIME_DIFF(NOW(),{Date Invoiced},‘d’)<90,"+60 days",
IF(DATETIME_DIFF(NOW(),{Date Invoiced},‘d’)>=90,"+90 days",“Error”)))))

ABS() is not needed.
AND() is not needed because the IF function stops when it gets a TRUE value.

[W_Vann_Hall]

Thanks for the streamlining. I was unaware you could short-circuit Airtable’s IF() function that way – it’s certainly going to make a few of my formulas easier to understand. (Here’s hoping that undocumented behavior doesn’t change in the future.) It seems as if I learn of a new shortcut daily, from yet another value that evaluates to ‘true’ to short-circuiting functions such as these…

As far as the calls to ABS() go, I had noted they could be eliminated if DATETIME_DIFF() and NOW() were swapped. I chose to keep them based on a brain misfire of my own: For some reason I thought the OP had referred to the date difference in terms of “days elapsed since ‘Date Invoiced’ and today,” rather than vice-versa. Knowing it can sometimes be less frustrating to force the software to adhere to the way one visualizes relationships than it is to revise one’s mental image to fit the software, I listed the variables with “Date Invoiced” first and wrapped the whole thing in an ABS. Clearly, though, both the OP and Airtable think alike, and the calls to ABS() can and should be tossed.

Thanks again!

[Andrew_Pulford]

Thank you both for your advice.

[Christoff]

[quote=W_Vann_Hall]I was unaware you could short-circuit Airtable’s IF() function that way – it’s certainly going to make a few of my formulas easier to understand. (Here’s hoping that undocumented behavior doesn’t change in the future.) Actually, it’s not “undocumented behavior”. It’s the way the IF function behaves.
IF(A,B,C) means: If A is true, then B. If A is not true, then C.
If A is true, then C is irrelevant. With nested IF statements, you can use this behavior to make the formula shorter but I think that it makes the formula harder to understand because it’s not obvious that there’s an upper and lower limit to the middle ranges of numbers being selected. For example, in my formula above “IF(DATETIME_DIFF(NOW(),{Date Invoiced},‘d’)<60” applies to records where “Date Invoiced” is from 30 days to 59 days ago. The “30 days” portion is not obvious.

Also, ABS() can interfere with numeric and date calculations: 30 days from now is not the same as 30 days ago, but adding the ABS function will make them the same, which is fine if that’s what you want.

And, it might be more intuitive to use TODAY() rather than NOW() when the time of day is not relevant in the calculation, although I don’t think that it makes any difference when calculating the number days between dates.

[W_Vann_Hall]

Yeah, the first time I looked at your code, I thought you were truly short-circuiting it by having it somehow break out of the nested IFs at the first ‘true’ condition met, even though later tests could also evaluate to ‘true.’ (Don’t ask me what I thought the algorithm was; I believe at the time I was juggling two callers and dealing with a cat that absolutely had to be on my lap right that moment.) Once things had quieted down, it dawned on me how you’d tweaked the sequence of date ranges. All-in-all, a far more elegant solution.

I also contemplated adding a check to make sure {Date Invoiced} <= TODAY(), but figured that was overkill. I’ve bumped into data integrity and validation questions on a couple of Airpress projects, and I wonder how others approach the issue. That seems to call for a new thread, though…