SharePoint Column - Conditional Formatting

Question

I basically have a calculated column that shows the date 1/29/1900 if there are some empty values in the other columns.

Is there a way to use JSON formatting to format it so that if the column displays 1/29/1900 it shows empty instead?

I was trying to mess with this code below but it's not working properly.

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "attributes": {
        "class": "=if(@currentField = 1/29/1900,'sp-field-severity--warning', '')"
    },
    "children": [
        {
            "elmType": "span",
            "style": {
                "display": "inline-block",
                "padding": "0 4px"
            },
            "attributes": {
                "iconName": "=if(@currentField == 1/29/1900,'Error', '')"
            }
        },
        {
            "elmType": "span",
            "txtContent": ""
        }
    ]
}

Answer 1

In calculated columns, you can set the data type returned from your formula in column settings at:

If return type of calculated field is Single line of text:

Use below JSON:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if(@currentField =='1/29/1900', '', @currentField)"
}

If return type of calculated field is Date and Time:

Use below JSON:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if(toLocaleDateString(@currentField) == '1/29/1900', '', @currentField)"
}

OR use below JSON if your actual dates in column will always be greater than 1/29/1900.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if(@currentField <= 1/29/1900, '', @currentField)"
}

Additionally you can apply CSS and classes to your formatted column by referring below Microsoft documentation:

Use column formatting to customize SharePoint

Answer 2

Try this:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if(@currentField =='1/29/1900', '', @currentField)"
}

Answer 3

You need to add apostrophe around the value 1/29/1900 in your code, also you need to change the txtconent attribute. Try to update your code like this:

   {
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "attributes": {
        "class": "=if(@currentField == '1/29/1900','sp-field-severity--warning', '')"
    },
    "children": [
        {
            "elmType": "span",
            "style": {
                "display": "inline-block",
                "padding": "0 4px"
            },
            "attributes": {
                "iconName": "=if(@currentField == '1/29/1900','Error', '')"
            }
        },
        {
            "elmType": "span",
            "txtContent": "=if(@currentField =='1/29/1900', '', @currentField)"
        }
    ]
}

Answer 4

Based on your field data type, you should set your value in your condition as below:

Check date value

"txtContent": "=if(Date(@currentField,toDateString())==1/29/1900,'',@currentField)"

Check text value

"txtContent": "=if(@currentField=='1/29/1900','',@currentField)"

Check also the full guide to work with JSON date format at JSON SharePoint Date Column Formatting in SharePoint 2019 and SharePoint Online