Nested IF Statement in SP List Calculated Column

Question

I need to create a Nested If statement based on several columns.

• Column 1. CurrentDate (Date)
• Column 2. DteofCQ (Date)
• Column 3. CQTitle (Text)
• Column 4. ComplianceStatus (Calculated)

There are 8 positions in the organization; 4 general positions (with various names), and Driver, Clerk, Cashier, Manager. The general positions have no recurring training. The others have differing sunset dates on their training; from 365 days (every year) to 1095 days (every three years).

What I need -

1. If the CQTitle column contains any of the general positions the Compliance Status should contain 'Certified', regardless of the contents of the DteofCQ

2. If the CQTitle column contains Driver, Clerk, Cashier, or Manager positions and the DteofCQ is blank Compliance Status should contain 'Noncompliant'

3. If the CQTitle column contains Driver, or Manager and the DteofCQ has a date the formula shall compare the contents of DteofCQ to the CurrentDate. Where the difference is greater the 365 days (it has been one year of more since last training) the Compliance Status should contain 'Noncompliant', else 'Compliant'

   • If CQTitle contains Cashier = 720 days
   • If CQTitle contains Manager = 1095 days

Can all of the above be contained within one Nested IF statement?

I gotten this far with the calculated ComplianceStatus column -

=IF(ISBLANK(DteofCQ),"Noncompliant",IF(CQTitle="Driver","Certified",IF(CurrentDate-DteofCQ<=365,"Compliant","Noncompliant")))

Answer 1

As you are handling quite complicate conditions to handle, I will not suggest using Nested IF as it will make the structure quite complicate and will become a pain to maintain.

Using Flow to add a switch case control to check the value of CQTitle field and redirects to different process depending on the title. And you can add further if condition to check more. Also you can simply add more if conditions to handle. Then finally update the column value with a required value.

Reference which may be helpful:https://sharepointmaven.com/3-ways-to-build-multiple-conditions-in-microsoft-flow/