0

I have an SharePoint list with columns:

  • Title (Single Line Text column)
  • DteofAwrd and CurrentDte (both date columns)
  • ComplianceStatus (calculated column)

I am using the below to determine compliance status. If DteofAwrd column is blank return "Noncompliant". This is working.

If Title is DteofAwrd.

Calculated Column Formula:

=IF(ISBLANK(DteofAwrd),"Noncompliant",IF(Title="Medical"&(DteofAwrd)+1095<(CurrentDate),"Compliant","Noncompliant"))

The above still returns are blank DteofAwrd as 'Noncompliant' as desired, however it calls all other Medical WITH A DATE regardless of date as 'Noncompliant', even though some award dates are as current as last month.

Improve this question

5 Answers 5

Reset to default
0

If I understood you requirement correctly, here is a sample for the combined one:

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

IF(OR(OR(OR(TitleNew="Firefighter I", TitleNew="Firefighter II"),TitleNew="Fire Officer I"),TitleNew="Fire Officer II"), "Certified", 
  
IF(ISBLANK(CurrentDte),"Noncompliant",IF(AND(TitleNew="Medical",DATEDIF(DteofAwrd,CurrentDte,"d")<10),"Compliant",IF(AND(TitleNew="SCBA",DATEDIF(DteofAwrd,CurrentDte,"d")<5),"Compliant","Noncompliant")))))

enter image description here

Improve this answer
1
  • Yes, sir; that did it. This is great. Thank you!
    – Curtis
    Commented Aug 9, 2020 at 17:28
0

Try like below (used <10 days in the example):

=IF(OR(ISBLANK([DteofAwrd]),ISBLANK([CurrentDte])), "Noncompliant", IF(AND([TitleNew]="Medical", DATEDIF([DteofAwrd],[CurrentDte],"d")<10), "Compliant","Noncompliant"))

enter image description here

Improve this answer
6
  • Yep, that did it. You're my hero, or shero. :-) Now, how do I write a Nested IF statement that considers Medical, along with: - SCBA (365 days) - Fitness (365 days) - Live Fire (every 720 days) - I used 1095 in the current formula for Medical, it got them all write. All Titles appear randomly and some multiple times in the CQTitle column.
    – Curtis
    Commented Aug 6, 2020 at 23:25
  • One last, if I may; I was reading through another Q&A on a similar formula and the answer included the use of =IF(Today() >= DATE(YEAR([ in the formula to represent the current date. I currently am trying to figure out how to write code that will update a CurrentDate column I have in my list. Write now the date will only update if there is a change to the item (or when forced by code). Is there a way to incorporate the =IF(Today() >= DATE(YEAR([ into what you've been providing that will allow me to eliminate my CurrentDate column and the need for that coding?
    – Curtis
    Commented Aug 9, 2020 at 17:48
  • Please define 'CurrentDate' - is it the date when a list item is created/updated? There are already two fields, created and modified, that you can use to represent CurrentDate. Also, as you indicated, you can use Today() function in your formula to get the current date and time
    – Matiur Rahman
    Commented Aug 10, 2020 at 21:40
  • Current Date should simply be the Current Date by default. Compliance is a moving target and based on the Current Date. Every day each is one day close to noncompliance unless they re-certify/qualify. As I understand, the only way to make the list keep the Current Date actually 'current' is for an action to place to the list or an item. That aside the only other way is to write script that forces an action every 24 hrs, that will then force the date to change - which, as you say, is already captured in Modified, Changed...somewhere.
    – Curtis
    Commented Aug 11, 2020 at 23:37
  • Your best option would be to use a Work Flow or a PS script that can be executed every day to update ComplianceStatus with the same logic that you have added to the Calculated column. As you have already mentioned, an action to be taken on the items for the Calculated formula to work and update the status.
    – Matiur Rahman
    Commented Aug 12, 2020 at 21:13
0

Yep, that did it. Now, how do I write a Nested IF statement that considers Medical, along with: - SCBA (365 days) - Fitness (365 days) - Live Fire (every 720 days) - I used 1095 in the current formula for Medical, it got them all write. All Titles appear randomly and some multiple times in the CQTitle column

Improve this answer
0

You just have to repeat. See below done for "SCBA" with 5-day condtion

=IF(OR(ISBLANK([DteofAwrd]),ISBLANK([CurrentDte])), "Noncompliant", IF(AND([TitleNew]="Medical", DATEDIF([DteofAwrd],[CurrentDte],"d")<10), "Compliant", IF(AND([TitleNew]="SCBA", DATEDIF([DteofAwrd],[CurrentDte],"d")<5), "Compliant","Noncompliant")))

enter image description here

Improve this answer
0

Yep, that did it. Fantastic. Thank you!

I now have two sub-issues. I have one column 'Titles' some of the Titles are one-time certifications, others are recurring qualifications. I created two compliance columns; the one we were just working on 'QualComplianceStatus' and 'CertsComplianceStatus'.

I currently use the following in the 'Certs' column to return whether those Titles are "Certified". It works. But, it leaves unwanted 'No' responses where the item is a 'Qual'.

Is there anyway to combine the two formula so that it looks at all the Titles based on the individual IF parameter returning the desire results in a single compliance column?

For the Cert the date is only relevant for its presence; is there a date in the field for a Cert or not, if yes "Certified". For a Qual the date is countdown. The desired formula should determined type of Title, compare it to data in Date of Award, compare that against Current date, deliver desired text response, "Certified", "Compliant", "Noncompliant" in the Compliance Status column.

CERTSCOMPLIANCECOLUMN

=IF(ISBLANK(DteofCQ),"Noncompliant",IF(CQTitle="Firefighter I","Certified",IF(CQTitle="Firefighter II","Certified",IF(CQTitle="Fire Officer I","Certified",IF(CQTitle="Fire Officer II","Certified",IF(CQTitle="Fire Officer III","Certified",IF(CQTitle="Fire Inspector I","Certified",IF(CQTitle="Fire Inspector II","Certified",IF(CQTitle="Fire Inspector III","Certified",IF(CQTitle="Live Fire Instructor","Certified",IF(CQTitle="Fire Instructor I","Certified",IF(CQTitle="Fire Instructor II","Certified",IF(CQTitle="Fire Officer I","Certified",IF(CQTitle="Fire Officer II","Certified",IF(CQTitle="Fire Officer III","Certified",IF(CQTitle="Driver Operator, Pumper","Certified",IF(CQTitle="Driver Operator, Ladder Truck","Certified",IF(CQTitle="Driver Operator, Mobile Water Supply","Certified",IF(CQTitle="Marina Firefighter","Certified",IF(CQTitle="Park Structural Fire Coordinator","Certified"))))))))))))))))))))

QUALSCOMPLIANCECOLUMN =IF(OR(ISBLANK(DteofCQ),ISBLANK(CurrentDate)),"Noncompliant",IF(AND(CQTitle="Medical",DATEDIF(DteofCQ,CurrentDate,"d")<1095),"Compliant",IF(AND(CQTitle="FitnessPEB",DATEDIF(DteofCQ,CurrentDate,"d")<365),"Compliant",IF(AND(CQTitle="Self-Contained Breathing Apparatus Fit Test",DATEDIF(DteofCQ,CurrentDate,"d")<365),"Compliant",IF(AND(CQTitle="Live Fire Refresher",DATEDIF(DteofCQ,CurrentDate,"d")<720),"Compliant","Noncompliant")))))

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.