SharePoint list conditional formatting based on a date

In this SharePoint Online tutorial, we will discuss what is conditional formatting in SharePoint online lists, and how to apply sharepoint list conditional formatting based on date. Finally, I will show you, how to delete conditional formatting based on a date in a SharePoint list.

I will show you here two examples of how to implement conditional formatting in a SharePoint Online list and also one example of sharepoint list conditional formatting based on another cell.

Conditional formatting in SharePoint list

Conditional formatting in SharePoint Online is a method that allows users to apply visual formatting to list items based on certain conditions. It helps users to highlight specific data or values and makes it easier to analyze information within the SharePoint list.

Conditional formatting allows us to define rows, that will change the appearance of a SharePoint list column based on conditions where it making easy to highlight certain values.

Example: Recently, I created a SharePoint Online list for a client based out in the United States of America. Based on the business requirement, I have created an Issue Tracker list in SharePoint Online. We stored information like:

• Issue ID
• Issue
• Issue Description
• Issue Image
• Issue Status
• Issue Assigned to
• Issue Start Date – Date and Time type column
• Issue Resolved Date – Date and Time type column

Here I have added two Date and time type, columns as Issue Start Date and Issue Resolved Date. I will show how to create conditional formatting rules to Issue Start Date like when the Issue started before this year if yes then we can highlight or color code and displays the list items that are presented in the list. Also, we can give Issue Start Date as between today and a particular date by giving conditions. We will see how to give conditions in the next process.

SharePoint list conditional formatting based on date

For creating a Conditional format based on date, we should have a Date and time type column in our SharePoint list.

There are two different ways to create Conditional formatting based on the date, they are:

• Create Conditional formatting based on the date using column settings.
• Create Conditional formatting based on the date using Format current view.

Create Conditional formatting based on a date using column settings

We will see how to create conditional formatting based on the date using column settings in a SharePoint Online modern list.

• Open your SharePoint Online list that we have created and then, click on the column, of Date and time type -> Select Column settings. Example: Issue Start Date

• It Expands with a dialog box, click on the Format this column

• In Format columns, Choose Conditional Formatting and on the same Conditional formatting click on the Manage rules as shown below:

• A dialog box appears with Conditional formatting, we will explain with some examples for adding rules:-
• Case 1: click on the +Add rule

• Now we will create a rule by if condition, IF:
  • Choose a column: we need to choose a column in which we create a rule. Example: We will choose Issue Start Date
  • Choose a Comparison: we will use a comparison of whether the column is equal to, before, not equal to, etc. Example: We will choose is before
  • Choose a type or value: We need to choose between, Today or A Specific date. Example We will choose A Specific date -> Select a date in calendar(Jan/01/2023)

• Show list item as:- We can set a color based on our requirement.
• Now we can see the applied condition based on a date, that the IssueStartDate column has color-coded based on the is before comparison with the 1/1/2023 date and displays the list item as highlighted as shown below:

• Case 2: Now we will add another rule, Click on the +Add rule, here we can add another rule. (Optional)

• Now we will create a rule by if condition:
  • Choose a column: we need to choose a column in which we create a rule. Example: We will choose the Issue Start Date
  • Choose a Comparison: we will use a comparison of whether the column is equal to, before, not equal to, etc. Example: We will choose is between
  • Choose a type or value: We need to choose both between, Today and A Specific date. Example: We will choose Today and A specific date -> Select a date in calendar(April/27/2023)

• Show list item as:- We can set a color based on our requirement.
• Click on the Save Button.
• Now we can see the applied condition based on a date, that the IssueStartDate column has color-coded based on the is between comparison with the today date and 4/27/2023 date and displays the list item as highlighted as shown below:

In the below screenshot, we can find that, we have created two conditions with a comparison of is before and is between of Issue Start Date column

• We have applied both the conditions above that are based on a date. Here the IssueStartDate column has color-coded based on the comparison of is before and is between comparison displays the SharePoint Online list item as highlighted as shown below:

This is how to create conditional formatting based on the date using SharePoint Online list column settings.

Create SharePoint List Conditional formatting based on the date using Format current view

We will see how to create a SharePoint list conditional formatting based on the date using the Format current view

• Open your SharePoint Online list that you have created, and then Now expand the All items drop down and click on the Format current view

• In Format view, Choose Conditional Formatting, and on the same Conditional formatting click on the Manage rules as shown below:

• A dialog box appears with Conditional formatting, click on the +Add rule

• Now we will create a rule by if condition, IF:
  • Choose a column: we need to choose a column in which we create a rule. Example: We will choose Issue Start Date
  • Choose a Comparison: we will use a comparison of whether the column is equal to, before, not equal to, etc. Example: We will choose is after
  • Choose a type or value: We need to choose between, Today or A Specific date. Example We will choose A Specific date -> Select a date in calendar(Jan/01/2023)

• Show list item as:- We can set a color based on our requirement.
• Now we can see the applied condition based on a date, that the IssueStartDate column has color-coded based on the is before comparison with the 1/1/2023 date and displays the list item as highlighted as shown below:

This is how to create conditional formatting based on the date using the Format current view

SharePoint Online list conditional formatting based on date

Here is another example of how to implement conditional formatting based on a date column in SharePoint Online list.

For Example, I have taken a SharePoint Online list which consists of different columns such as Payment ID, Date of Payment, Method of Payment, Paid to, etc. To implement conditional formatting based on date, I chose the Date of Payment column which is the Date data type column.

Here, I will show you how to implement conditional formatting based on a date in the SharePoint Online list.

• Open a SharePoint Online list and click on the date column then click on Column settings and select Format this column. Inside that tab click on Manage rules under Conditional formatting.

• Then in the conditional formatting window click on + Add rule. Here, I have added 5 rules as listed below:

Rule 1: If

• Choose column: Date of Payment
• Choose a Comparision: is before
• Choose or Type a value: Selected specific date 6/27/2023
• Show list item as: Blue

Rule 2: If

• Choose column: Date of Payment
• Choose a Comparision: is between
• Choose or Type a value: Selected specific dates 6/21/2023 & 6/28/2023.
• Show list item as: Green

Rule 3: If

• Choose column: Date of Payment
• Choose a Comparision: is on or before
• Choose or Type a value: Selected specific dates 7/3/2023
• Show list item as: Orange

Rule 4: If

• Choose column: Date of Payment
• Choose a Comparision: is equal to
• Choose or Type a value: Selected specific dates 7/6/2023
• Show list item as: Pink

Rule 5: If

• Choose column: Date of Payment
• Choose a Comparision: is on or after
• Choose or Type a value: Selected specific date 7/7/2023
• Show list item as: Violet

Click on the Save button. After implementing conditional formatting based on a number column of the SharePoint list appears as shown below.

This is how to implement conditional formatting based on a number column in the SharePoint Online list.

SharePoint list conditional formatting based on another cell

Let us see, how to implement sharepoint list conditional formatting based on another cell. Here you can see below I have a SharePoint list having a few columns. Based on the Amount Paid column, we will change the color of the Donated Date column, like, if the Amount Paid is Yes, we will change the Donated Date column color to red color.

Now, let us see step by step how to implement sharepoint list conditional formatting based on another column.

• FirstOpen the SharePoint Online list and then expand the column (Donated Date), where you want to add the conditional formatting.
• Click on the Column settings -> Select Format this column.

• Next, it will open the Format Donated Date Columns -> Click on Format columns and select Conditional Formatting.
• In the manage rules of conditional formatting, we can add the condition statement depending on the other columns.

• Click on the Manage rules and add the condition which formats the column depending on other columns.
  • For Example, we have taken the Amount Paid (Choice) column as a reference. (If the Amount paid is equal to No, then the Donated Date will fill with color next to No)
• Add Conditions in the Edit Rule, and select the Show list item value.
• In the Show list item as we can choose our desired color for the condition and click on Save.

• After this, it will show the Donated column regarding No value in the Amount Paid as filled with color like below:

Here we saw an example of sharepoint list conditional formatting based on another cell.

Delete a SharePoint list conditional formatting based on a date

We already applied the conditional format based on the date in the above examples. We will see how to delete conditional formatting from a SharePoint Online list.

• Click on the column, to which you applied the conditional format of Date and time type, -> Select Column settings. Example: Issue Start Date
• It Expands with a dialog box, click on the Format this column
• In Format columns, Choose Conditional Formatting and on the same Conditional formatting click on the Manage rules.
• A dialog box appears with Conditional formatting, where we applied the condition -> click on the color based as shown below:

• Click on the No styles.

• Then, Click on the more options(…), as shown below:

• Click on the delete rule.

• Then, click on the Save button. Now we can see the applied condition is deleted based on a date, that the IssueStartDate column has color-coded based on the comparison as shown below:

This is how we can delete the conditional formatting based on a date in a SharePoint Online list.

Conclusion

In this SharePoint Online tutorial, we discussed sharepoint list conditional formatting based on date, how to create conditional formatting based on a date in SharePoint online list, and also how to delete conditional formatting based on a date in SharePoint online list.

You may also like:

• Add Comments to SharePoint Online List Item
• Create a Calendar View in SharePoint List
• Add a Link to SharePoint Document Library
• Add Attachments Column in SharePoint List