JSON SharePoint Date Column Formatting in SharePoint 2019 and SharePoint Online

JSON SharePoint Date Column Formatting

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.

JSON SharePoint Date Column Formatting in SharePoint 2019 and SharePoint Online
SharePoint Date Column Formatting

What’s JSON Column Formatting In SharePoint?

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.

JSON column formatting in SharePoint 2019
JSON SharePoint Date Column Formatting

The column formatting does not change the data, it just changes its style!

What’s Required Permission to use JSON Column Formatting in SharePoint 2019?

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.


Can we use JSON Column Formatting for SharePoint 2016?

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.
Missing JSON column formatting in SharePoint 2016
Missing JSON Column Formatting in SharePoint 2016

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.

Switch from advanced mode to design mode in JSON Column Formatting

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

JSON column formatting Design Mode

You can also edit the design mode template to specify your color formatting based on before, after and equal to conditions as shown below:

Edit Template JSON column formatting Design Mode

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.

JSON column formatting Design Mode

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.

JSON column formatting in SharePoint 2019
SharePoint Date Column Formatting JSON Mode

You may also like to check JSON Column Formatting Samples on GitHub


Using JSON Column Formatting in SharePoint 2019

There are two ways to add JSON Column Formatting for a specific column in SharePoint List.

  • Column Heading.
  • Column Setting.

Column Heading: Format Column in SharePoint List

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“.
JSON column formatting in SharePoint Online
Format Column using JSON in SharePoint 2019
  • Add your JSON Object.
  • Preview and Save, then click on the above close button.
JSON column formatting in SharePoint 2019

Column Settings: Format Column in SharePoint List

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”.
JSON column formatting setting in SharePoint 2019
Column Formatting Settings

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.

Please enter valid column formatting json
Please Ener Valid Column-Formatting JSON

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:

error JSON column formatting in SharePoint
Output of Date Conditional Formatting In SharePoint 2019

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.


SharePoint Date Column Formatting in SharePoint 2019 and SharePoint Online

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.

@now function in JSON Column Formatting

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:

Only show date without time in JSON column formatting

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*1000

Consider 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'))))"

SharePoint Date Column Formatting JSON Code

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'))))"
  }
SharePoint Date Column Conditional Formatting Output
Date Column using JSON Column Formatting in SharePoint 2019 as well as SharePoint Online
Date Conditional Formatting In SharePoint 2019

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:

Applies To
  • SharePoint 2019.
  • SharePoint Online.
Download JSON Column Formatting Samples

You can download JSON Column Formatting Samples on GitHub

You may also like to read

Leave a Reply