In this post, we’re gonna learn how to use JSON Column Formatting to perform SharePoint Date Column Formatting in SharePoint 2019 as well as SharePoint Online.
- 1 What’s JSON Column Formatting In SharePoint?
-
2
SharePoint Date Column Formatting in SharePoint 2019 and SharePoint Online
-
2.1
JSON Date Column Conditional Formatting Scenario
- 2.1.1 JSON Code Formatting Template
- 2.1.2 Check if current date equal to Today in JSON Column Formatting
- 2.1.3 Check if current date after Today + N days in JSON Column Formatting
- 2.1.4 Check if current date before Today – N days in JSON Column Formatting
- 2.1.5 Check if current date is NULL in JSON Column Formatting
- 2.1.6 Nested IF in JSON Column Formatting
- 2.1.7 SharePoint Date Column Formatting JSON Code
- 2.2 Apply JSON Column Formatting based on another Date Field
-
2.1
JSON Date Column Conditional Formatting Scenario
SharePoint JSON Column Formatting is a new feature in SharePoint Server 2019 and SharePoint Online that helps you to format and customize how the SharePoint List Column is displayed using JSON Object.
The column formatting does not change the data, it just changes its style!
If you have sufficient permission to create and manage a list/view, you will be able to use JSON Column Formatting in SharePoint Online and SharePoint 2019.
Unfortunately, the JSON Column Formatting is not available for SharePoint 2016 as well as SharePoint 2013.
JSON Column Formatting is ONLY supported for
- SharePoint 2019.
- SharePoint Online.
You might also like to read Missing Column Formatting in SharePoint 2016.
JSON Column Formatting Modes
As per the column data type, there are two modes for JSON Column Formatting:
- Design Mode.
- Advanced Mode.
Note: Switching from Advance Mode to Design mode will discard the current JSON formatting code.
Design Mode
It’s the default JSON Column Formatting mode, it’s a simple mode, there is No JSON code required! But in most cases, it may not match the complex conditional formatting requirements
You can also edit the design mode template to specify your color formatting based on before, after and equal to conditions as shown below:
Advanced Mode
This mode is not a simple mode like Design Mode, and of course, it requires JSON code. However, it is the most used mode and a suitable option for complex column formatting requirements.
You can easily switch from design mode to advanced mode by clicking on the below “Advanced Mode” link.
After switching to Advanced Mode, the predefined conditions in the design mode will be converted to JSON code.
{
"elmType": "div",
"style": {
"padding": "0 4px"
},
"attributes": {
"class": {
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
"@currentField",
""
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "<",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-backgroundColor-warningBackground",
{
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-backgroundColor-successBackground",
{
"operator": ":",
"operands": [
{
"operator": ">",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-backgroundColor-errorBackground",
""
]
}
]
}
]
}
]
}
},
"txtContent": "@currentField"
}Actually, you can start from here and customize it or write your own new JSON code that matches your requirements.
You may also like to check JSON Column Formatting Samples on GitHub
There are two ways to add JSON Column Formatting for a specific column in SharePoint List.
- Column Heading.
- Column Setting.
It’s the preferred way to add JSON Column Formatting code to a List Column where you can preview your changes before saving your code!
Steps
- From the Column Heading,
- Select “Column Setting“,
- Click on “Format this column“.
- Add your JSON Object.
- Preview and Save, then click on the above close button.
Steps
- Open the List Settings.
- Below the “Columns” section, click on the column that you would like to format it.
- In the “Column setting”, you would find “Column Formatting”.
In this way, you can’t preview your JSON Formatting Code!
Validate JSON Column Formatting Code
Actually, there is no advanced option to validate the JSON code in JSON Column Formatting.
If there is an error in the JSON code, you will only get a message that informs you the code formatting has an error. But it will not inform you where’s exactly the error.
Additionally, if there are any errors in the “style” section like writing a wrong rule or missing single brackets, you will not get any warning message. Moreover, the column data will be disappeared as shown below:
Therefore, it would prefer to validate your JSON code using any online tool over the internet to know exactly what’s the error and in which line you get it.
In this section, we’ll provide a JSON code to perform SharePoint Date Column Formatting in SharePoint 2019 Column List.
JSON Date Column Conditional Formatting Scenario
Consider you have a Date column in SharePoint List and you would like to perform conditional formatting as per the below rules:
- If the current date equal to Today, highlight the background with blue.
- If the current date before Today – N days, highlight the background with orange.
- If the current date after Today + N days, highlight the background with green.
- If the current date is NULL or Blank or Empty, highlight the background with purple.
JSON Code Formatting Template
Below is the default template for applying a style for the current field.
{
"$schema":"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType":"div",
"txtContent":"@currentField",
"style":{
"CSS-Property":""
}
}The CSS property should be like “background-color”, “Color” ….etc.
Background-Color property in JSON Column Formatting
{
"$schema":"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType":"div",
"txtContent":"@currentField",
"style":{
"background-color":""
}
}Check if current date equal to Today in JSON Column Formatting
below is code for highlighting the background field if the current field equal to Today.
if(@currentField == @now,'#70F1F9','white')Unfrothantly, the above code will not work as expected, where @now will get the current date with the current time.
In this case, the @now value will never match the @currentField value.
Therefore, to compare the current date field with Today, you should use “Date()” function with “toDateString()” to ignore the time section and show only the date.
if(@currentField == Date(@now,toDateString()),'#70F1F9','white')Don’t forget to exclude the time from the column setting as the following:
Check if current date after Today + N days in JSON Column Formatting
Before providing the JSON code, you should first know how you can add or subtract N days to @now in JSON Column Formatting.
Calculate N Days in JSON Column Formatting
You can add or subtract N days from the current date in milliseconds like the following:
@now + N * 24*60*60*1000Consider now, you need to add or subtract N= 10 days from the current date, so the value should be 10 * (24*60*60*1000) = 864000000
In the end, the final code should be like
if(@currentField >= @now + 864000000,'#28FD35','white')Check if current date before Today – N days in JSON Column Formatting
In this example, we will check if the date is less than or equal to Today – 10 days.
if(@currentField <= @now - 864000000,'#28FD35','white')Check if current date is NULL in JSON Column Formatting
One of the tricks in JSON Column Formatting is checking if the current field is NULL or Empty or Blank.
In this case, you should use “Number()” function to get the field length. therefore, the current field is blank if it’s equal zero.
if(Number(@currentField)==0, '#CE70F9','white')Nested IF in JSON Column Formatting
In previous sections, we have performed all conditions required to apply background-color style for current field in SharePoint List.
In this section, we’re gonna combine all conditions using Nested-If as the following:
"=if(Number(@currentField)==0, '#CE70F9',if(@currentField >= @now + 864000000,'#28FD35',if(@currentField <= @now - 864000000,'#F9C570',if(@currentField == Date(@now,toDateString()),'#70F1F9','white'))))"The final JSON code for SharePoint Date Column formatting should look like:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(Number(@currentField)==0, '#CE70F9',if(@currentField >= @now + 864000000,'#28FD35',if(@currentField <= @now - 864000000,'#F9C570',if(@currentField == Date(@now,toDateString()),'#70F1F9','white'))))"
}Apply JSON Column Formatting based on another Date Field
You can perform SharePoint Date Column Formatting based on another field value in JSON Column Formatting as the following:
If you have a column called (DueDate), the code should be
if(Number( [$DueDate] )==0, '#CE70F9','white')Note: if there is space in the column field, so it should be replaced with “ _x0020_“. a field named (Due Date) should be referenced as [$Due_x0020_Date].
Conclusion
In conclusion, we have learned how to apply SharePoint date column formatting in SharePoint 2019 and SharePoint Online using JSON Column Formatting.
Additionally, we’ve answered the below JSON Column Formatting questions:
- What’s JSON Column Formatting In SharePoint?
- What’s the required Permission to add JSON Column Formatting Code?
- Is JSON Column Formatting supported for SharePoint 2016 / 2013?
- Wha’s JSON Column Formatting Modes?
- How to use JSON Column Formatting in SharePoint?
- How to Validate JSON Column Formatting Code?
- How to Apply Date Column Conditional Formatting in SharePoint List?
- How to apply JSON Column Formatting based on another Field?
- Download JSON Column Formatting Code Samples.
Applies To
- SharePoint 2019.
- SharePoint Online.
Download JSON Column Formatting Samples
You can download JSON Column Formatting Samples on GitHub