IF Statements where two conditions have to be true

[Rae_Groshan]

Hello!
I am working on writing a formula, and running into a few issues.

To preface, I am tracking employee trainings in this database. We have 3 trainings that expire. 2 expire after 2 years and 1 after 4 years. I am trying to write an if/then statement where if the training name matches one of those 3 and it’s within a specific time frame, it will output “Overdue”, or “Up to Date”.

Logical statements that I need:

1. If the training name = “First Aid” or “CPI” AND the Days field is greater than 730 days, the training is overdue.
2. If the training name = “Notary Public” And the Days field is greater than 1460, the training is overdue.

I’ve been working on it for a bit, and have a first attempt at the logical statement, but it’s not working. Any ideas would be beyond helpful!

IF(
AND(
{Trainings} = “First Aid”,
{Trainings} = “CPI”
)&
IF(
{Days} > 730,
“Overdue”,
“Up to date”
)

[Kamille_Parks]

When nesting formulas be sure to include all necessary closing parenthesis. You’re also using AND() when OR() would be more appropriate. {Trainings} cannot equal “First Aid” and “CPI” at the same time. You will need an AND() statement, just not where you put it:

IF(
    OR(
        AND(
            OR({Trainings} = "First Aid", {Trainings} = "CPI"),
            {Days} > 730
        ),
        AND(
            {Trainings} = "Notary Public",
            {Days} > 1460
        )
    ),
    "Overdue",
    "Up to Date"
)

[Rae_Groshan]

Thank you for your help. I have played around with what you provided, and it’s not working. Every field is being calculated as “Up to Date”, even when it is over the time limit.

I tried playing around with the layout using some examples from the Airtable documentation pages with no luck.

Do you have any ideas on why it’s calculating every single one as “Up to Date”?

Here are a few of the variations I tried with no luck (I apologize for any formatting issues, I cannot seem to be able to get it to indent):
1.
IF(
AND(
OR(
{Training} = “Mandatory Reporter”,
{Training} = “Confidentiality (HIPAA)”)
),
“Up to Date”
IF(
AND(
{Training} = “Notary Public”,
{Days} > 1460
),
“Overdue”,
“Up to Date”
IF(
AND(
OR(
{Training} = “CPI”, {Training} = “First Aid”,
{Days} > 730)
),
“Overdue”,
“Up to Date”
)
)
)

IF(
AND(
{Training} = “Notary Public”,
{Days} > 1460
),
“Overdue”,

IF(
OR(
{Training} = “CPI”,
{Training} = “First Aid”,
{Days} > 730
),
"Overdue”,
“Up to date”
)
)

[Kamille_Parks]

The formula I gave works for me, which tells me the problem is your data or how you’ve described your problem. This is what I get when I enter the exact formula I gave above.

[Rae_Groshan]

So the way we have our tables set up is different. I have the date of training as a column, a column to transform it into datetime format (which I will later hide), the days function (calculates the # of days from the date of training to today using the datetime_diff function), and then the calculation 2 field is the work in progress I posted about. Calculation 2 has the formula you created in it.

I assume since days is a function for me, that that might be why it’s not working? Is there a better way for me to go about this? Thank you again for everything!

[Kamille_Parks]

Rae_Groshan:

a column to transform it into datetime forma

Why does this column exist? Your {Date of Training} field is already a date-type field, meaning any calculation you would need to do in the {Days} column is almost certainly easier to do based of it than it would be from the date converted into a string. {Date in DT Format} seems redundant.

Rae_Groshan:

I assume since days is a function for me, that that might be why it’s not working?

Nope. As long as the output is a number and not a string, the fact that {Days} is a formula-type field is irrelevant. I replicated your setup and it worked for me. Check your table for typos?

Rae_Groshan:

Is there a better way for me to go about this?

Yes. Since {Training} links to another table, include in that table a number field for the maximum number of days that type of training should allow (730, 1460, etc.). In the table you’ve been screenshotting include a lookup or rollup field to bring in the {Max Number of Days} field. Then the {Calculation 2}'s formula would be much, much simpler:

IF(
    {Days} > {Max Number of Days},
    "Overdue",
    "Up to Date"
)

[Rae_Groshan]

You are right. That column was redundant so I removed it and fixed my days function. I’m still learning my way around Airtable, so my error there.

The max # of days lookup field idea was beyond helpful. Thank you so much for everything!