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?

# Set one field based on another date

**W_Vann_Hall**#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().

**Christoff**#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.

**W_Vann_Hall**#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!

**Christoff**#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.

**W_Vann_Hall**#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âŚ