Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 04, 2021 09:15 AM
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:
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”
)
Aug 04, 2021 09:42 AM
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"
)
Aug 04, 2021 12:28 PM
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”
)
)
Aug 04, 2021 12:51 PM
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.
Aug 04, 2021 02:02 PM
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!
Aug 04, 2021 03:30 PM
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.
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?
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"
)
Aug 05, 2021 07:20 AM
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!