1

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?

2 Answers 2

0

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:

enter image description here

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:

enter image description here

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.

7
  • 1
    Thank you so much!!!!!!! :)
    – user104986
    Aug 25, 2022 at 16:57
  • So sorry to bother but I'm just realizing the formatting doesn't work; the requirement is that if the Request Completed Date column is empty for 1 day, 7 days, or 8 days, then the column colors should change respectively in severity levels.
    – user104986
    Aug 25, 2022 at 17:55
  • Do you mean: color code only if date field is empty for 1 day, 7 days, or 8 days from "Created" date? Aug 26, 2022 at 3:06
  • Yep, exactly! Color code only if the Request Completed Date field has been empty for 1 day, 7 days, or 8 days from the Request Date.
    – user104986
    Aug 31, 2022 at 13:33
  • Try expression like this: =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. Aug 31, 2022 at 13:39
0

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', '')"
  }
}

demo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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