Conditional Formatting SharePoint List

%3CLINGO-SUB%20id%3D%22lingo-sub-1774277%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1774277%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20your%20support%20with%20conditional%20JSON%20formatting%20of%20SharePoint%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20format%20column%20INVEST_DUE_DATE%20based%20on%20current%20date%20(date%20cell)%20and%20based%20on%20value%20in%20column%20Phase%20(string).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AnatolyD_0-1602583576170.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226250i48764A05F9833BAD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AnatolyD_0-1602583576170.png%22%20alt%3D%22AnatolyD_0-1602583576170.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERight%20now%2C%20I%20implemented%20just%20formatting%20based%20on%20date%2C%20using%20following%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3CBR%20%2F%3E%22%24schema%22%3A%20%22%3CA%20href%3D%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%3C%2FA%3E%22%2C%3CBR%20%2F%3E%22elmType%22%3A%20%22div%22%2C%3CBR%20%2F%3E%22txtContent%22%3A%20%22%40currentField%22%2C%3CBR%20%2F%3E%22style%22%3A%20%7B%3CBR%20%2F%3E%22background-color%22%3A%20%22%3Dif(%40currentField%20%26lt%3B%20%40now%20%2C%20'%23f7adad'%2C%20if(%40currentField%20%26lt%3B%3D%20%40now%20%2B%20864000000%2C%20'%23f2d596'%2C%20if(%40currentField%20%26gt%3B%3D%20%40now%20%2B%20864000000%20%2C%20'%23a0dec0'%2C%20'')))%22%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20apply%20additional%20condition%20for%20it%20-%20use%20color%20formatting%20only%20if%20value%20of%20cell%20Phase%20%3D%20Investigation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20%2C%20it%20should%20be%20something%20like%20this%3C%2FP%3E%3CP%3E(%40currentField%20%26lt%3B%20%40now)%20and%26nbsp%3B%40%5B%24Phase%5D%3D'Investigation'%20%2C%20'%23f7adad'%2C%26nbsp%3B%20and%20etc....%3C%2FP%3E%3CP%3EBut%20it%20does%20not%20works....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20advise%20me%20how%20to%20modify%20initial%20JSON%20code%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1774277%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edate%20and%20text%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EJson%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EJSON%20formatting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1774441%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1774441%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830217%22%20target%3D%22_blank%22%3E%40AnatolyD%3C%2FA%3E%2C%20You%20need%20to%20use%20the%20%3CSTRONG%3E%26amp%3B%26amp%3B%26nbsp%3B%3C%2FSTRONG%3E(AND)%20operator%20for%20this.%20use%20your%20conditions%20in%20below%20format%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%3Dif((%40currentField%20%26lt%3B%20%40now)%20%26amp%3B%26amp%3B%20%5B%24Phase%5D%3D'Investigation'%2C%20'%23f7adad'%2C'%23f2d596')%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20%3CSTRONG%3E%24Phase%26nbsp%3B%3C%2FSTRONG%3Eis%20the%20internal%20name%20of%20your%20Phase%20column.%3C%2FP%3E%3CP%3E%3CSTRONG%3EReferences%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%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%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%20solve%20your%20issue.%20This%20will%20help%20others%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20content%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%3CLINGO-SUB%20id%3D%22lingo-sub-1774471%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1774471%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373269%22%20target%3D%22_blank%22%3E%40ganeshsanap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello!%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%20one%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%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%22txtContent%22%3A%20%22%40currentField%22%2C%0A%22style%22%3A%20%7B%0A%22background-color%22%3A%20%22%3Dif((%40currentField%20%26lt%3B%20%40now)%20%26amp%3B%26amp%3B%20%5BPHASE%5D%3D'Investigation'%2C%20'%23f7adad'%2C%20if((%40currentField%20%26lt%3B%3D%20%40now%20%2B%20864000000)%20%26amp%3B%26amp%3B%20%5BPHASE%5D%3D'Investigation'%2C%20'%23f2d596'%2C%20if((%40currentField%20%26gt%3B%3D%20%40now%20%2B%20864000000)%20%26amp%3B%26amp%3B%20%5BPHASE%5D%3D'Investigation'%20%2C%20'%23a0dec0'%2C%20'')))%22%0A%7D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20something%20is%20wrong....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20values%20disappear%2C%20like%20with%20a%20wrong%20condition.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AnatolyD_0-1602589999787.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226263i182A8E1DCBDBA207%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AnatolyD_0-1602589999787.png%22%20alt%3D%22AnatolyD_0-1602589999787.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20proposed%20an%20option%20for%202%20colors%2C%20but%20I%20m%20using%203%20colors.%3C%2FP%3E%3CP%3ESorry%2C%20I%20m%20not%20familiar%20with%20this%20syntax....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20name%20is%20%22PHASE%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AnatolyD_1-1602590078403.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226264i3E042ED636D29208%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AnatolyD_1-1602590078403.png%22%20alt%3D%22AnatolyD_1-1602590078403.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1774496%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1774496%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830217%22%20target%3D%22_blank%22%3E%40AnatolyD%3C%2FA%3E%26nbsp%3Bwhen%20you're%20referencing%20a%20SharePoint%20column%20in%20JSON%20the%20column%20name%20must%20always%20have%20a%20%24%20symbol%20before%20the%20column%20name%2C%20so%20you%20need%20to%20change%20%5BPHASE%5D%20to%20%5B%24PHASE%5D.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello team,

 

I need your support with conditional JSON formatting of SharePoint list.

 

I would like to format column INVEST_DUE_DATE based on current date (date cell) and based on value in column Phase (string).

 

AnatolyD_0-1602583576170.png

 

Right now, I implemented just formatting based on date, using following code:

 

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField < @now , '#f7adad', if(@currentField <= @now + 864000000, '#f2d596', if(@currentField >= @now + 864000000 , '#a0dec0', '')))"
}
}

 

I want to apply additional condition for it - use color formatting only if value of cell Phase = Investigation.

 

So , it should be something like this

(@currentField < @now) and @[$Phase]='Investigation' , '#f7adad',  and etc....

But it does not works....

 

Could you please advise me how to modify initial JSON code?

 

Thank you in advance! 

 

 

5 Replies
Highlighted

@AnatolyD, You need to use the && (AND) operator for this. use your conditions in below format: 

 

 

=if((@currentField < @now) && [$Phase]='Investigation', '#f7adad','#f2d596')

 

 

Where Phase is the internal name of your "Phase" column with $ (needed in JSON formatting to add reference to column).

ReferencesUse column formatting to customize SharePoint 


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

 

Highlighted

@ganeshsanap 

Hello! thanks.

 

I tried this one

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if((@currentField < @now) && [PHASE]='Investigation', '#f7adad', if((@currentField <= @now + 864000000) && [PHASE]='Investigation', '#f2d596', if((@currentField >= @now + 864000000) && [PHASE]='Investigation' , '#a0dec0', '')))"
}
}

 

But something is wrong....

 

Column values disappear, like with a wrong condition.

 

AnatolyD_0-1602589999787.png

 

You proposed an option for 2 colors, but I m using 3 colors.

Sorry, I m not familiar with this syntax....

 

Column name is "PHASE"

 

AnatolyD_1-1602590078403.png

 

Highlighted

@AnatolyD when you're referencing a SharePoint column in JSON the column name must always have a $ symbol before the column name, so you need to change [PHASE] to [$PHASE].

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Highlighted

@AnatolyD , Use below condition:

 

 

=if((@currentField < @now) && [$PHASE]='Investigation', '#f7adad', if((@currentField <= @now + 864000000) && [$PHASE]='Investigation', '#f2d596', if((@currentField >= @now + 864000000) && [$PHASE]='Investigation' , '#a0dec0', '')))

 

 

If you want to apply the background color only if PHASE="Investigation" then you can use below condition as well:

 

 

=if([$PHASE]='Investigation', if(@currentField < @now, '#f7adad', if(@currentField <= (@now + 864000000), '#f2d596', if(@currentField >= (@now + 864000000), '#a0dec0', ''))), '')

 


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

 

Highlighted

@ganeshsanap @Rob Elliott 

 

Hello!

Sorry, may be I m a little dumb, but

something is still wrong.

 

AnatolyD_0-1602593321529.png

 

I tried both ways

 

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if([$PHASE]='Investigation', if(@currentField < @now, '#f7adad', if(@currentField <= (@now + 864000000), '#f2d596', if(@currentField >= (@now + 864000000), '#a0dec0', ''))) , '')"
}
}

and

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if((@currentField < @now) && [$PHASE]='Investigation', '#f7adad', if((@currentField <= @now + 864000000) && [$PHASE]='Investigation', '#f2d596', if((@currentField >= @now + 864000000) && [$PHASE]='Investigation' , '#a0dec0', '')))"
}
}

 

PHASE has a single line of text type, not Choice.

 

AnatolyD_1-1602593470535.png