Need assistance with syntax for merging two statements

[Rae_Groshan]

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?

[Alexey_Gusev]

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

[Rae_Groshan]

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.

[Alexey_Gusev]

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?

[Rae_Groshan]

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!

[Alexey_Gusev]

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