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 -
If the CQTitle column contains any of the general positions the Compliance Status should contain 'Certified', regardless of the contents of the DteofCQ
If the CQTitle column contains Driver, Clerk, Cashier, or Manager positions and the DteofCQ is blank Compliance Status should contain 'Noncompliant'
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 -