Sharepoint List Conditional column formatting based on date range

%3CLINGO-SUB%20id%3D%22lingo-sub-2042416%22%20slang%3D%22en-US%22%3ESharepoint%20List%20Conditional%20column%20formatting%20based%20on%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2042416%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20you%20clever%20people%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20get%20a%20DUE%20DATE%20column%20in%20my%20list%20to%20show%20red%20when%20the%20BID%20STATUS%20is%20'Go'%2C%20and%20the%20DUE%20DATE%20is%20within%205%20days%20from%20now.%20To%20test%20it%2C%20we%20coded%20it%20so%20that%20it%20would%20colour%20any%20future%20dates.%20However%2C%20it's%20not%20colouring%20anything.%20Here%20is%20the%20code%20we%20have%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%7B%0A%22%24schema%22%3A%20%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%22%2C%0A%22elmType%22%3A%20%22div%22%2C%0A%22attributes%22%3A%20%7B%0A%22class%22%3A%20%22%3Dif(((Number(%5B%24Submission_x0020_due_x0020_date%5D)%20-%20Number(%40now))%20%26gt%3B%200)%20%26amp%3B%26amp%3B%20%5B%24Bid_x0020_status%5D%20%3D%3D%20'Go'%2C'sp-css-backgroundColor-blockingBackground50'%2C%20'')%22%0A%7D%2C%0A%22children%22%3A%20%5B%0A%7B%0A%22elmType%22%3A%20%22span%22%2C%0A%22style%22%3A%20%7B%0A%22display%22%3A%20%22inline-block%22%2C%0A%22padding%22%3A%20%220%204px%22%0A%7D%0A%7D%2C%0A%7B%0A%22elmType%22%3A%20%22span%22%2C%0A%22txtContent%22%3A%20%22%40currentField%22%0A%7D%0A%5D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20Can%20anyone%20advise%20what%20we%20are%20doing%20wrong%3F%20And%20why%20none%20of%20the%20dates%20are%20showing%20red%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2042416%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Lists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2042545%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20Conditional%20column%20formatting%20based%20on%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2042545%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F916229%22%20target%3D%22_blank%22%3E%40MonsoonDesign%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20using%20below%20JSON%20code%2C%20it%20should%20work%20for%20you%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%7B%0A%20%20%20%20%22%24schema%22%3A%20%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%22%2C%0A%20%20%20%20%22elmType%22%3A%20%22div%22%2C%0A%20%20%20%20%22attributes%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22class%22%3A%20%22%3Dif((%5B%24Submission_x0020_due_x0020_date%5D%20%26gt%3B%3D%20%40now%20%26amp%3B%26amp%3B%20%5B%24Submission_x0020_due_x0020_date%5D%20%26lt%3B%3D%20%40now%20%2B%20432000000)%20%26amp%3B%26amp%3B%20%5B%24Bid_x0020_status%5D%20%3D%3D%20'Go'%2C%20'sp-css-backgroundColor-blockingBackground50'%2C%20'')%22%0A%20%20%20%20%7D%2C%0A%20%20%20%20%22children%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22elmType%22%3A%20%22span%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22style%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22display%22%3A%20%22inline-block%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22padding%22%3A%20%220%204px%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22elmType%22%3A%20%22span%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22txtContent%22%3A%20%22%40currentField%22%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%5D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMake%20sure%20you%20are%20using%20correct%26nbsp%3B%3CSTRONG%3Einternal%20name%3C%2FSTRONG%3E%20of%20Submission%20due%20date%20and%20Bid%20status%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EMicrosoft%20documentation%3C%2FSTRONG%3E%3A%26nbsp%3B%3CA%20title%3D%22Use%20column%20formatting%20to%20customize%20SharePoint%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fdeclarative-customization%2Fcolumn-formatting%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EUse%20column%20formatting%20to%20customize%20SharePoint%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%20%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20to%20solve%20your%20issue.%20This%20will%20help%20others%20to%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20post%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3ELike%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi you clever people,

I am trying to get a DUE DATE column in my list to show red when the BID STATUS is 'Go', and the DUE DATE is within 5 days from now. To test it, we coded it so that it would colour any future dates. However, it's not colouring anything. Here is the code we have:

 

 

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"attributes": {
"class": "=if(((Number([$Submission_x0020_due_x0020_date]) - Number(@now)) > 0) && [$Bid_x0020_status] == 'Go','sp-css-backgroundColor-blockingBackground50', '')"
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}

 

 

  Can anyone advise what we are doing wrong? And why none of the dates are showing red?

 

1 Reply

@MonsoonDesign 

 

Try using below JSON code, it should work for you:

 

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "attributes": {
        "class": "=if(([$Submission_x0020_due_x0020_date] >= @now && [$Submission_x0020_due_x0020_date] <= @now + 432000000) && [$Bid_x0020_status] == 'Go', 'sp-css-backgroundColor-blockingBackground50', '')"
    },
    "children": [
        {
            "elmType": "span",
            "style": {
                "display": "inline-block",
                "padding": "0 4px"
            }
        },
        {
            "elmType": "span",
            "txtContent": "@currentField"
        }
    ]
}

 

Make sure you are using correct internal name of Submission due date and Bid status columns.

 

Microsoft documentationUse column formatting to customize SharePoint 


Please click Mark as Best Response if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.