SharePoint List Conditional Formatting Based on Date (Rows, Columns and JSON)

Dates are usually the most important columns in a SharePoint list. When a leave has started, a contract is about to expire, or a project is overdue, you want people to see it without reading every row.

In this tutorial, I’ll show you three real examples: coloring whole rows by a start date, coloring a single date column, and coloring rows by the number of days between two dates. For each one, I’ll use the no-code rule builder where it works and JSON where you need more control.

Quick answer: For simple rules, open the view menu and select Format current view (rows) or open a column’s menu and select Column settings > Format this column, then choose Conditional formatting > Manage rules > Add rule. For rules like “due in the next 7 days”, use Advanced mode and compare the date with @now or addDays(@now, 7).

Before you start

• Formatting changes only how the list looks. It never changes the data, and anyone who can create and manage views can set it up.
• If conditional formatting is new to you, start with the basics of SharePoint list conditional formatting, then come back for the date rules.
• In JSON, you refer to a column by its internal name, like [$DueDate]. If you created the column with spaces, the internal name uses _x0020_, so “Due Date” becomes [$Due_x0020_Date]. To check it, open List settings, select the column, and look at the end of the browser address (Field=…).
• A column you use in JSON must be included in the view.
• @now is the current date and time when someone loads the view, so the colors update every day on their own. Date math is in milliseconds, but addDays() saves you from writing 86400000.

A tip I use on every list: create date columns without spaces (DueDate) and rename them afterwards (Due Date). The internal name stays short, which makes the JSON much easier to read.

If your dates show in the wrong format before you even start, see how to change the date format in SharePoint Online.

Example 1: Color the entire row based on a start date

My first list is an Employee Leave Tracker with Employee Name, Leave Type (Choice), Leave Start Date and Leave End Date. The manager wants to see at a glance which leaves have already started and which start soon:

Leave Start Date	Row color
Today or in the past	Red
In the next 7 days	Yellow
More than 7 days away	Green

Option A: Use the rule builder

Open the list, select the view name (for example, All Items) and select Format current view.

In the panel, select Conditional formatting, then Manage rules.

Select + Add rule. Choose Leave Start Date, the condition is on or before and the value Today. Then select the pencil icon under Show list item as, pick a light red background, and select Save.

Every leave that has started now shows in red:

Add the second and third rules the same way for the upcoming leaves. Keep the rules from overlapping, so a row never matches two of them at once, and check each one against a few test dates.

Option B: Use JSON for an exact 7-day window

“Within the next 7 days” moves every day, which is hard to express with fixed dates in the rule builder. JSON handles it in one line. In the Format view panel, select Advanced mode, replace the code with the following, and select Save:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if([$LeaveStartDate] == '', '', if([$LeaveStartDate] <= @now, 'sp-field-severity--blocked', if([$LeaveStartDate] <= addDays(@now, 7), 'sp-field-severity--warning', 'sp-field-severity--good')))"
}

Here’s what it does, from left to right:

• If the start date is blank, the row keeps its normal look.
• If the start date is on or before right now, the row gets the red sp-field-severity--blocked class.
• If it’s within the next 7 days, it gets the yellow sp-field-severity--warning class.
• Anything later gets the green sp-field-severity--good class.

For example, if today is October 5, 2026, a leave starting October 2 is red, one starting October 9 is yellow, and one starting October 20 is green. Change the 7 to any number of days you like.

Want more than a background color, such as bold text or extra styling for the row? See SharePoint list view customization using JSON.

Microsoft’s documentation notes that additionalRowClass works only when the JSON has no rowFormatter. If you paste a sample that has both, the row colors are ignored, which is a common reason people think their JSON is broken.

Example 2: Color only the date column

Sometimes you only want the date cell to stand out. My second list is a Vendor Contract Renewal Tracker with Vendor Name, Contract Owner, Contract Start Date and Contract End Date:

Contract End Date	Cell color
Before today (expired)	Red
In the next 30 days	Yellow
More than 30 days away	Green

Option A: Use the rule builder

Select the Contract End Date column header, then Column settings > Format this column.

Select Conditional formatting > Manage rules.

Add a rule: Contract End Date is before Today, with a red background, and save it.

Expired contracts now have a red end date:

Then add the rules for contracts that expire soon and later, again making sure they don’t overlap.

Option B: Use JSON for a rolling 30 days

In Format this column, select Advanced mode and paste this. Because it’s column formatting, @currentField is the Contract End Date itself:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField.displayValue",
  "style": { "padding": "4px 8px" },
  "attributes": {
    "class": "=if(@currentField == '', '', if(@currentField < @now, 'sp-field-severity--blocked', if(@currentField <= addDays(@now, 30), 'sp-field-severity--warning', 'sp-field-severity--good')))"
  }
}

@currentField.displayValue shows the date the same way SharePoint normally does, so the cell still reads like a date. Change the 30 to 60 or 90 if your team needs more notice for renewals.

For more column examples, such as icons and data bars, see how to add SharePoint column formatting with JSON.

Example 3: Color rows by the number of days between two dates

My third list is a Project Tracker with Project Name, Start Date, Due Date, Status and Project Owner. The project manager wants short projects flagged, because those are the ones that slip:

Duration (Due Date minus Start Date)	Color
Less than 5 days	Red
5 to 9 days	Orange
10 days or more	Green

Method 1: Calculated columns and the rule builder

First, add a calculated column named Duration. In List settings, select Create column, choose Calculated (calculation based on other columns), enter this formula and set the data type returned to Number:

If you haven’t made one before, here’s how to create a calculated column in SharePoint.

=[Due Date]-[Start Date]

Now, rules like “is less than 5” and “is less than 10” overlap, because 3 is less than both. To avoid that, I add one more calculated column, DurationBand, that returns a single line of text:

=IF([Duration]<5,"Short",IF([Duration]<10,"Medium","Long"))

Then, on the Due Date column, select Format this column > Conditional formatting and add three rules on DurationBand with is equal to: Short is red, Medium is orange and Long is green. Each project matches exactly one rule.

Calculated columns can’t use a live today’s date, so they’re perfect for the duration but not for “overdue”. For more formulas, see SharePoint calculated column date formulas.

Method 2: JSON that also flags overdue projects

JSON can combine the duration with today’s date. Open Format current view > Advanced mode and paste this. It assumes the internal names are DueDate and Duration:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if([$DueDate] != '' && [$DueDate] < @now, 'sp-field-severity--blocked', if(Number([$Duration]) < 5, 'sp-field-severity--severeWarning', if(Number([$Duration]) < 10, 'sp-field-severity--warning', 'sp-field-severity--good')))"
}

Overdue projects come first and show in red. The rest are colored by duration: orange under 5 days, yellow under 10 days, and green for 10 days or more. If your columns have spaces in their internal names, replace [$DueDate] with [$Due_x0020_Date].

You can also skip the Duration column and compute it in the JSON with (Number([$DueDate]) - Number([$StartDate])) / 86400000, but the calculated column is easier to read, sort and filter.

For projects, you may also like a timeline instead of colored rows. Try a Gantt chart view in a modern SharePoint list.

Troubleshooting

• Nothing changes: check the internal column names and make sure those columns are in the view.
• Row colors are ignored: remove any rowFormatter from the JSON. It overrides additionalRowClass.
• All the other columns disappeared: your JSON has a rowFormatter that only shows @currentField, which in a row formatter is the Title. Use additionalRowClass instead.
• Blank dates show as red: add the == '' check at the start of the expression, as in the samples above.
• Off by a day: @now includes the current time. To compare whole days, compare with addDays(@now, -1) or adjust the number of days.
• Calendar views: the same rules work in a calendar view. See color coding in the SharePoint calendar view.

For more ways to format SharePoint lists, try these tutorials:

• SharePoint Online List View + SharePoint List Calendar View Formatting
• SharePoint Calculated Column IF Statement: 7 Examples
• SharePoint List View Formatting by JSON Using Lookup Value
• How to Customize Modern SharePoint List Form Using JSON Formatting?

Frequently Asked Questions