1

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": ""
        }
    ]
}
1
  • What is the return type of your calculated field? Single line of text or Date & time? – Ganesh Sanap Dec 18 '20 at 5:04
1

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

enter image description here

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

0

Try this:

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

0

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)"
        }
    ]
}
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.