Set one field based on another date

#1

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?

#2

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().

#3

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.

#4

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!

#5

#6

[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.) [/quote]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.

#7

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âŚ