AnswerBun.com

Nested IF Statement in SP List Calculated Column

SharePoint Asked on December 14, 2021

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

One Answer

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/

Answered by Jerry_MSFT on December 14, 2021

Add your own answers!

Related Questions

Add QuickLinks Webpart by using pnp powershell

1  Asked on October 26, 2021 by abdelkader_ak

     

Reading configuration at runtime

1  Asked on October 25, 2021 by milind

         

what went wrong with my sharepoint server deployment?

0  Asked on October 25, 2021 by sharepointadmin

     

Ask a Question

Get help from others!

© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP