Check Multiple IF Statement Formula

[Phineas]

Wondering if anyone can looks this over and let me know whether I need to make any correction before building it in my SharePoint list calculated column? The output should be 'Certified', 'Compliant', or 'Noncompliant' based on current date and the date of 'Date of CQ plus renewal frequencies.

It all worked when I built in it Excel (using the column and row numbers instead of the list column headings. I'm concerned most about capturing the 'current date' to compare against the 'Date of CQ'.

=IF(ISBLANK(DateOfCQ),"Noncompliant",

IF(OR(OR(OR(OR(OR(OR(OR(

CERTQUALTITLE="Driver I",CERTQUALTITLE="Driver II"),

CERTQUALTITLE="Officer"),

CERTQUALTITLE="Attendent"),

CERTQUALTITLE="Usher"),"Certified",

IF(AND(CERTQUALTITLE="Maintenance Tech",DateOfCQ+1095>=TODAY()),"Compliant",

IF(AND(CERTQUALTITLE=" Maintenance Supervisor",DateOfCQ+365>=TODAY()),"Compliant",

IF(AND(CERTQUALTITLE="Asst Manager",DateOfCQ+730>=TODAY()),"Compliant",

IF(AND(CERTQUALTITLE="Manager",DateOfCQ+365>=TODAY()),"Compliant","Noncompliant"))))))

[WarrenBelz · Accepted solution]

@Phineas ,

Are you asking about interaction outside Power Apps (SharePoint) or inside? For the first, you would need a SharePoint calculated column, but the second could be done with the structure of the code provided wherever you wanted to display it in Power Apps.

[Phineas]

Yes, sir.

There is a SharePoint list in which all the certs and quals data is stored (a Library holds the actual documents).

The member's log into the Power Apps to review their documents (Screen 1, w/Power Apps Gallery), upload a new document to the SharePoint certs and quals SharePoint list (Screen 2 w/upload Power Apps Form), or update existing document in the same SharePoint list (Screen 3 w/Power Apps Form).

The formula we've been kicking back and forth is being used  in the Power Apps Gallery to determine compliance of all documents (within the Power Apps environment only), and a similar version of said formula to trigger the Power Apps Toggle control (for Member overall compliance - which updates the 'Member List').

I have an additional formula that I am using in the SharePoint list to show the status of all those documents in the SharePoint. I use this calculated column when I run a Collection through the Power Apps to tally ALL of the members and ALL of the documents to determine and display 'Program' compliance by National, Region, Local.

Am I correct in my assumption that this ^ final component above is static in SharePoint unless another action is taken upon each of the items in the SharePoint (the SharePoint does not 'auto-update' itself, correct)? I'd need to run a flow across the entire SharePoint list to keep it updated every 24 hours, or each item would need to be individually updated through Power Apps or directly in the SharePoint list in order for Today() to register and compliance to be recalculated and remain accurate, yes?

Example:

Today is 12/6/2021. I need a 'Driver' cert in my profile (in the SharePoint List) to be compliant. I upload a document; however, the document expires tomorrow 12/7/2021. Today the Power Apps will show me compliant. Also, because the action on my item/and the list took place today I show compliant. However, when I access the Power Apps tomorrow the same document will show compliant in Power Apps but not in SharePoint because no triggering action in the SharePoint list hase occurred to update the Today() or the recalculation in SharePoint, is this correct?

What keeps the entire SharePoint list accurate everyday? A calculation only triggers in SharePoint when a change action occurs in SharePoint, yes? If no edit it made to the SharePoint list the data in SharePoint doesn't update, correct? And, this is why I need the flow, yes?

[WarrenBelz]

@Phineas ,

I don't use SharePoint calculated columns much, but my understanding is that Today() in SharePoint is the current day and opening a SharePoint view will result in the calculated column showing the calculation as at the current day (a change action should not be required).