Formula Help! If statement with date

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,"")

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

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}
    )
)