Recently we had a project that deployed a simple, creative solution that we thought might be relevant for our readers.
The use case is managing agreements in a document library on SharePoint. We’re going to deviate a bit from their specific use case so we can generalize it a bit. Our example today will be based on contractor agreements.
The premise is straightforward—each contractor and organization has a formal agreement valid for one year. Contractors are onboarded at various points of the year, so the dates are always unique.
We need a way to see agreements coming up for renewal to know which ones to contact to get the correct information before their expiration, as contractors with an expired agreement are not allowed on the job site.
To start, we’ve created a Contractor Agreements Document Library.
You can see that our document library has several metadata columns on it—this information gets entered when we upload the agreement upon signature. Here is a breakdown of the columns you see:
- Name – The document title. We’ve adopted a standard naming convention of [Contractor Name] – Contractor Agreement
- Status – Represents the agreement as Active or Renewed. Once a new agreement is signed, it’s uploaded, and the previous one is updated to Renewed.
- Effective Date – Represents the date the agreement began.
- Expiration Date – Represents the date the agreement will expire.
- Days Until Expiry – This calculated field subtracts the Expiration Date from today’s date to show the number of days remaining.
The field has been given a formula only to calculate the status = Active; otherwise, it will do nothing, which you can see in the screenshot’s red box.
The formula for this is =if(Status=”Active”,[Expiration Date]-Today(),””)
These are all of the columns we need at this point. There could be others that you would add based on your use case. SharePoint provides various field types to choose from, so you can build out metadata around your document (or on your list, if you’re building a list instead) as you see fit.
We want to embed some conditional formatting to highlight columns or the entire row based on some of that metadata. To do this, we’ll click the drop-down next to the column on which we want to establish our conditional formatting. In our example, we want to do the following:
- If the Agreement is going to expire within 11-30 days, highlight the Days Until Expiry field yellow
- If the Agreement is going to expire within ten days, highlight the entire row red
First, select the column of metadata you want to base your formatting on and click the drop-down next to the column title. Select Column settings and then Format this column, as shown in the screenshot.
A flyout will open on the right side, presenting two options: Format view and Format columns
First, we want to add the logic to highlight the column cell yellow if the day is within the 11-30 range.
To do this, click Manage Rules as seen on the screenshot above, ensuring the Choose Column has the correct column noted within it. A new menu will open on the flyout.
Click Add rule (not shown in screenshots) to display the rule logic and fill in the criteria. In our example, we need two conditions as follows:
- If Days Until Expiry is greater than or equal to 11
- If Days Until Expiry is less than or equal to 30
In the Show list item as area we select the yellow tone and click Save.
Next, we want to add the conditional logic to highlight the entire row red if the Days Until Expiry field has a value of 10 or less. To do this, we return to the Conditional Formatting menu and select Format View.
We click Add Rule (not shown in screenshots) to get into the rule editor.
The process is much the same for formatting columns as we did above. Build out the rule logic on the provided menu, as shown in the screenshot, then click Save (not shown).
Now that we’ve added our conditions, we can test them out. The screenshot below shows the Days Until Expiry column highlighted yellow because the number is between 11 and 30.
The screenshot below shows the entire row highlighted as red because the Days Until Expiry number is less than 10.
Now, when our team reviews this document library, they’ll quickly see which ones are coming up, including those close to expiry.
There you have it! A great way to leverage the conditional formatting features of SharePoint. Note that these features exist for both the Document Library and List functions.
Questions about how you could be maximizing SharePoint for your organization? Contact C5 Insight today!
