SharePoint JSON: Formatting List View with Empty Column and Date Value

Question

I'm totally new to JSON formatting and need your help.

The SP list requires the following:

List formatting to show colors (green, yellow, red) if the Request Completed Date is empty as well as if the Request Date exceeds 1 day, 7 days, and 8 days.

Essentially, green = 1 day, 7 days = yellow, and more than 8 days = red.

Is this possible?

Answer 1

You can use JSON column formatting for this requirements. Use JSON like (considering today's date is 8/24/2022):

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "attributes": {
        "class": "=if(Number(@currentField) == 0 , '', if(@currentField + 691200000 < @now , 'ms-bgColor-red ms-fontColor-white', if(@currentField + 604800000 < @now , 'ms-bgColor-yellow ms-fontColor-white', if(@currentField + 86400000 < @now , 'ms-bgColor-green ms-fontColor-white', ''))))"
    }
}

Output:

OR:

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "attributes": {
        "class": "=if(Number(@currentField) == 0 , '', if(@currentField + 604800000 < @now , 'ms-bgColor-red ms-fontColor-white', if(@currentField + 518400000 < @now , 'ms-bgColor-yellow ms-fontColor-white', if(@currentField < @now , 'ms-bgColor-green ms-fontColor-white', ''))))"
    }
}

Output:

References:

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

---

You can enhance the JSON as per your further requirements, you can check if date column is empty or not using expression Number(@currentField) == 0.

And you have to convert number of days to milliseconds to compare with current date (@now).

Example: 1 day = 1 x 24 x 60 x 60 x 1000 = 86400000

---

Update from comments:

Try using this JSON:

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "attributes": {
        "class": "=if(Number(@currentField) == 0 , if([$RequestDate] + 604800000 < @now , 'ms-bgColor-red ms-fontColor-white', if([$RequestDate] + 518400000 < @now , 'ms-bgColor-yellow ms-fontColor-white', if([$RequestDate] < @now , 'ms-bgColor-green ms-fontColor-white', ''))), '')"
    }
}

Use correct internal name of Request Date column in place of [$RequestDate] in JSON expression in format: [$InternalNameOfColumn]. You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?

You have to apply this JSON formatting for "Request Completed Date" column.

Answer 2

As a supplement, you could use the addDays() function to add days directly without counting seconds. It can also be compared to other columns, not just the current time. For example below:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if(@currentField > addDays(Date([$A]), 1), '#00ff00', '')"
  }
}