SharePoint JSON formatting: Check if date & time column is blank/empty

SharePoint JSON formatting is a great way to customize how columns/fields are displayed in SharePoint list/library views. Many times you want to customize SharePoint list columns (like Status or Expiry of list item) based on other date & time columns in your list. While doing so, when you try to check if date & time column is blank/empty using below expression, it won’t work as expected:

"txtContent": "=if([$DueDate]) == '', '', [$DueDate])"

So, to check if date & time column is blank/empty using SharePoint JSON formatting, you have to use either of below workarounds:

Using Number() function

You can use Number(DateTimeColumn) == 0 to check if the date & time column is blank or not.

Example
{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "=if(Number([$DueDate]) == 0, 'Blank Date', if([$DueDate] < @now, 'Expired', 'Active'))",
    "attributes": {
        "class": "=if(Number([$DueDate]) == 0, 'sp-field-severity--warning', if([$DueDate] < @now, 'sp-field-severity--blocked', 'sp-field-severity--good'))"
    }
}
Output
SharePoint JSON formatting – Check if date and time column is blank or empty

Advertisementshttps://c0.pubmine.com/sf/0.0.3/html/safeframe.htmlREPORT THIS ADPRIVACY

You can also find above JSON at list formatting samples on GitHub: Formatting a column when a date column is blank

Using toString() function

You can use toString(DateTimeColumn) == '' to check if the date & time column is blank or not.

Example
{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "=if(toString([$DueDate]) == '', 'Blank Date', if([$DueDate] < @now, 'Expired', 'Active'))",
    "attributes": {
        "class": "=if(toString([$DueDate]) == '', 'sp-field-severity--warning', if([$DueDate] < @now, 'sp-field-severity--blocked', 'sp-field-severity--good'))"
    }
}

Simplest way

You can also check if date & time column is blank/empty using below JSON:

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "=if(@currentField, @currentField, 'Blank Date')",
    "attributes": {
        "class": "=if(@currentField, 'sp-field-severity--good', 'sp-field-severity--warning')"
    }
}
Output
SharePoint JSON formatting – Check if date and time column is blank or empty

This blog is part of SharePoint Week. For more great content, click here

About the Author:

Ganesh Sanap is a Software Engineer having expertise in SharePoint, Microsoft 365, Power Platform and Azure Development.

He is an active contributor on SharePoint StackExchange, Microsoft Techcommunity and Power Platform community as he likes to help the community. He likes to stay up to date with newly released and coming features in Microsoft 365 and Azure Services.

He is a Microsoft Certified Professional holding certifications on Microsoft 365, Power Platform and Microsoft Azure AI.

Currently he is mainly focused on SharePoint Framework, Power Platform and Azure AI development.

Reference:

Sanap, G. (2021). SharePoint admin center: New columns on Active sites page. Available at: https://ganeshsanapblogs.wordpress.com/2021/05/31/sharepoint-admin-center-new-columns-on-active-sites-page/ [Accessed: 30th August 2021].

Share this on...

Rate this Post:

Share: