Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 24, 2021 01:53 PM
I have a table where I am tracking employee trainings. I have this formula to output whether the staff member is up to date on a certain training:
IF({Days} = BLANK(), “Overdue”,
IF({Days} > {Max # of Days (from Training)}, “Overdue”, “Up to Date”))
I am trying to add another option. I have another formula that determines whether the staff member is almost due for training:
IF(AND({Days} < {Max # of Days (from Training)}, {Days} > {Overdue Threshold (from Training)}), “Soon to be overdue”)
I was wondering if I could get help with the syntax to combine the two formulas into one?
Aug 31, 2021 08:50 PM
Hi,
I suppose ‘Max#ofDays’ > ‘Overdue Threshold’. I would use smth like:
SWITCH(
(2-
({Days}>{Max # of Days (from Training)})
-({Days} > {Overdue Threshold (from Training)})
)
*({Days} != BLANK()),
0,‘overdue’, 1 ,‘soon’, 2 ,‘up_to_date’)
additional advise - when I struggle with hard formula, i usually copy that part of table to some ‘playground’, and give fields short simple letter names to make formula easier ))
Sep 07, 2021 03:22 PM
Hi thanks for responding and my apologies for the delay in response. I was on vacation. Unfortunately that doesn’t work with my use case. I’m attaching images to show what those columns actually look like.
The first image shows the training name, and the columns Max # of Days and Overdue Threshold.
The second image shows the two equations that I mentioned in my initial post at work. Status is the first function listed and Needs Renewal is the second. I’m trying to combine the syntax of the the two so that it’s only one column.
Hopefully this makes more sense. Thanks again for the help. I’ll play more around with reworking the SWITCH function idea when I get back into the office tomorrow.
Sep 09, 2021 03:47 PM
Hi,
almost clear, and i think that’s quite easy. but I can’t understand what’s the desired result. Should second function “override” result of first?
Sep 10, 2021 07:38 AM
I guess instead of override, I wanted it to be another possibility. So the first function would have “Overdue”, “Up to date”, and “Soon to be overdue” as possible values. I couldn’t figure out how to have a third option (confused by Airtable syntax), hence me making the second function. I hope this makes more sense, and thank you!
Sep 10, 2021 11:20 AM
Well, we are talking about different. You can have unlimited number or options (almost, i think formula size limit exists).
What i wanted to know, in short, what value should be ‘final’ for ‘Notary Public’ in your case?
‘Up to Date’? obviously no
override it by 'Soon to be overdue"?
Or smth like “'Up to Date,'Soon to be overdue” (also possible)
btw, my formula should work, but it may be even easier,i just hurried
if my formula shows error try on similar text fields.
Lookup fields may add trouble, as it counted as [arrays]. For text, ‘concatenate’ or ‘arrayjoin’ used at first. for number, try 0+{fieldname} instead of {fieldname} in formula, that explicitly converts array to number value.
in common, that
SWITCH(
(a>3)+
(a>12)+
(a>19).
0,baby,1,kid,2,teenager,3,man) , that’s for “range” cases of same value
for independent values, it may be:
( get 3 Y/N answers, and divide into 8 options by one formula)
SWITCH(
4*(answ1=‘yes’)+
2*(answ1=‘yes’)+
(answ1=‘yes’),
0,‘first opt’,1,'second…, 7, ‘eight option’)
i think you can manage it now))