Formula Help! If statement with date

[Tricia_Harding]

I want to create an if statement that returns the value from a cell if all parameters are true.

Basically, finding for field 4, it’s an "if the checkbox in field 1 is yes and the invoice paid date in field 2 is after 12/31/18 and before 1/1/20, then the amount is equal to field 3. I know I’m close, but I keep getting an error message.

Can anyone help?

Field 1 = coach/payee paid
Field 2 = invoice paid date
Field 3 = amount payable to coach
Field 4 = $ amount that counts toward 2019 revenue

[Christopher_Bre]

This should be the formula

IF(AND(IS_BEFORE({invoice paid date},‘1/1/20’),IS_AFTER({invoice paid date},‘12/31/18’),coach/payee paid=1),amount payable to coach,"")

[Tricia_Harding]

Thank you! Will try that this week and let you know.

[Justin_Barrett]

Unfortunately there are some errors with the formula that @Christopher_Brennick listed. First, comparing a date field against a string containing a date won’t work. Airtable requires date comparisons to be against datetime objects, and strings with dates aren’t automatically converted to datetime objects. DATETIME_PARSE() is necessary to do that conversion.

Second, a couple field references are missing their surrounding curly braces. I’m not sure if the field names you listed are the literal names of your fields, but any field name that contains multiple words or certain special characters must be wrapped in curly braces in order to work in a formula.

While these next two items aren’t errors, they’re more optimal ways of dealing with certain items. First, when checking a checkbox field, there’s no need to see if that field is equal to anything. Just put the field reference by itself. If it’s checked, that part of the comparison will evaluate to TRUE. Otherwise it will evaluate to FALSE.

Finally, it’s best to not include an empty string at the end of an IF function if you want the field to remain blank if the rest of the comparisons don’t check out. If that last piece is omitted, Airtable will automatically fill in an equivalent version of BLANK() that matches the other data being output. By using an empty string, you force the output to always be a string. In this case, you want a currency value, so leaving the ending string out makes it possible to format the formula field as currency.

With all that, the formula would look like this:

IF(
    AND(
        IS_BEFORE(
            {invoice paid date},
            DATETIME_PARSE('1/1/20', 'L')
        ),
        IS_AFTER(
            {invoice paid date},
            DATETIME_PARSE('12/31/18', 'L')
        ),
        {coach/payee paid}
    ),
    {amount payable to coach}
)

If you want to avoid seeing #ERROR when there’s no date in the {invoice paid date} field, use this:

IF(
    {invoice paid date},
    IF(
        AND(
            IS_BEFORE(
                {invoice paid date},
                DATETIME_PARSE('1/1/20', 'L')
            ),
            IS_AFTER(
                {invoice paid date},
                DATETIME_PARSE('12/31/18', 'L')
            ),
            {coach/payee paid}
        ),
        {amount payable to coach}
    )
)