SOLVED

Sharepoint List Column Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2484911%22%20slang%3D%22en-US%22%3ESharepoint%20List%20Column%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2484911%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone.%20I%20have%20a%20Sharepoint%20Online%20list%20with%20various%20different%20fields%20holding%20contract%20information.%20Each%20row%20contains%20several%20date%20fields%20formatted%20dd%2Fmm%2Fyyyy.%20I%20want%20every%20row%20that%20has%20an%20expired%20date%20to%20be%20formatted%20in%20light%20red%2C%20not%20just%20the%20date%20field.%20So%20if%20either%20one%20of%20the%20date%20fields%20have%20expired%20the%20whole%20row%20will%20be%20formatted.%20There%20are%20several%20date%20fields%20and%20some%20do%20not%20contain%20any%20information%20therefore%20I%20need%20something%20to%20make%20sure%20nothing%20happens%20if%20the%20field%20does%20not%20contain%20a%20date.%20One%20of%20my%20date%20fields%20are%20called%20%22ISO%2045001%20(OHAS%2018001)%20so%20I%20am%20also%20unsure%20how%20to%20reference%20this%2C%20will%20it%20understand%20the%20spacing%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20looked%20at%20many%20formuns%20but%20wasn't%20making%20any%20progress.%20Thank%20you%20to%20anyone%20who%20can%20help%20me%20out%20here.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%2C%3CBR%20%2F%3E%3CBR%20%2F%3ESion%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2484911%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2486430%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20Column%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2486430%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1087947%22%20target%3D%22_blank%22%3E%40DevHuws%3C%2FA%3E%26nbsp%3BYou%20need%20to%20use%20%3CSTRONG%3EJSON%20view%20formatting%3C%2FSTRONG%3E%20for%20your%20requirements.%20Use%20JSON%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%7B%0A%20%20%22%24schema%22%3A%20%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fview-formatting.schema.json%22%2C%0A%20%20%20%22additionalRowClass%22%3A%20%22%3Dif(%5B%24DueDate%5D%20%26lt%3B%3D%20%40now%20%7C%7C%20%5B%24EndDate%5D%20%26lt%3B%3D%20%40now%20%7C%7C%20%5B%24AnyOtherDateColumn%5D%20%26lt%3B%3D%20%40now%2C%20'sp-field-severity--severeWarning'%2C%20'')%22%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20DueDate%2C%20EndDate%2C%20AnyOtherDateColumn%20are%26nbsp%3B%3CA%20title%3D%22internal%20names%22%20href%3D%22https%3A%2F%2Fplumsail.com%2Fdocs%2Fhelp-desk-o365%2Fv1.x%2FHow%2520To%2FFind%2520the%2520internal%2520name%2520of%2520SharePoint%2520column.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Einternal%20names%3C%2FA%3E%26nbsp%3Bof%20SharePoint%20date%20columns.%20As%20JSON%20formatting%20supports%20internal%20names%20only%2C%20you%20have%20to%20find%20out%20the%20internal%20name%20of%20your%26nbsp%3B%3CSPAN%3E%22ISO%2045001%20(OHAS%2018001)%22%20column%20using%20above%20link.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAlso%2C%20you%20can%20add%20multiple%20date%20conditions%20using%20OR%20(%20%7C%7C%20)%20operator%20in%20single%26nbsp%3B%3CSTRONG%3Eif%3C%2FSTRONG%3E%20expressions%20as%20shown%20above.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EMicrosoft%3C%2FSTRONG%3E%20documentation%3A%26nbsp%3B%3CA%20title%3D%22Use%20view%20formatting%20to%20customize%20SharePoint%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fdeclarative-customization%2Fview-formatting%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EUse%20view%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%20%26amp%3B%20%3CSTRONG%3ELike%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2492328%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20Column%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2492328%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%3BHi%20thanks%20for%20the%20reply%2C%20this%20works%20well%20with%20formatting%20based%20on%20multiple%20columns%2C%20however%20I%20have%20date%20fields%20which%20do%20not%20contain%20dates%20and%20these%20are%20being%20formatted%20as%20well.%20Is%20there%20anyway%20to%20include%20if%20the%20date%20field%20%3D%200%2C%20it%20will%20ignore%20it%3F%20While%20still%20formatting%20all%20that%20have%20late%20dates%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2492407%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20Column%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2492407%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1087947%22%20target%3D%22_blank%22%3E%40DevHuws%3C%2FA%3E%26nbsp%3BGreat%2C%20glad%20it%20worked%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20your%20another%20question%20about%20blank%20date%20field%3A%3C%2FP%3E%3CP%3EThis%20is%20not%20possible%20using%20the%20view%20formatting%20code%20I%20provided%20as%20it%20colors%20the%20whole%20row.%20However%2C%20you%20can%20override%20the%20view%20formatting%20by%20applying%26nbsp%3B%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fdeclarative-customization%2Fcolumn-formatting%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%3Ecolumn%20formatting%3C%2FA%3E%26nbsp%3Bfor%20specific%20date%20columns.%20You%20can%20use%20the%20JSON%20similar%20to%20this%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%22txtContent%22%3A%20%22%40currentField%22%2C%0A%20%20%20%20%22style%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22background-color%22%3A%20%22%3Dif(Number(%40currentField)%20%3D%3D%200%2C%20'white'%2C%20'%23FDE7E9')%22%0A%20%20%20%20%7D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20change%20the%20background-color%20in%20above%20JSON%20as%20per%20your%20requirements.%20The%20output%20of%20above%20JSON%20will%20look%20something%20like%20below%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ganeshsanap_0-1624885175147.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291891i191B8BF9B7CD838D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ganeshsanap_0-1624885175147.png%22%20alt%3D%22ganeshsanap_0-1624885175147.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EMore%20ways%20to%20check%20if%20date%20field%20is%20blank%3A%26nbsp%3B%3CA%20title%3D%22SharePoint%20JSON%20formatting%3A%20Check%20if%20date%20%26amp%3B%20time%20column%20is%20blank%2Fempty%22%20href%3D%22https%3A%2F%2Fganeshsanapblogs.wordpress.com%2F2021%2F06%2F20%2Fsharepoint-json-formatting-check-if-date-and-time-column-is-blank-empty%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3ESharePoint%20JSON%20formatting%3A%20Check%20if%20date%20%26amp%3B%20time%20column%20is%20blank%2Fempty%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%26nbsp%3B%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%26nbsp%3B%26amp%3B%20%3CSTRONG%3ELike%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%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone. I have a Sharepoint Online list with various different fields holding contract information. Each row contains several date fields formatted dd/mm/yyyy. I want every row that has an expired date to be formatted in light red, not just the date field. So if either one of the date fields have expired the whole row will be formatted. There are several date fields and some do not contain any information therefore I need something to make sure nothing happens if the field does not contain a date. One of my date fields are called "ISO 45001 (OHAS 18001) so I am also unsure how to reference this, will it understand the spacing?

I have looked at many formuns but wasn't making any progress. Thank you to anyone who can help me out here.

Thanks,

Sion

5 Replies

@DevHuws You need to use JSON view formatting for your requirements. Use JSON in below format: 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
   "additionalRowClass": "=if([$DueDate] <= @now || [$EndDate] <= @now || [$AnyOtherDateColumn] <= @now, 'sp-field-severity--severeWarning', '')"
}

 

Where DueDate, EndDate, AnyOtherDateColumn are internal names of SharePoint date columns. As JSON formatting supports internal names only, you have to find out the internal name of your "ISO 45001 (OHAS 18001)" column using above link.

 

Also, you can add multiple date conditions using OR ( || ) operator in single if expressions as shown above.

Microsoft documentation: Use view formatting to customize SharePoint 


Please click Mark as Best Response & Like 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.

@ganeshsanap Hi thanks for the reply, this works well with formatting based on multiple columns, however I have date fields which do not contain dates and these are being formatted as well. Is there anyway to include if the date field = 0, it will ignore it? While still formatting all that have late dates?

@DevHuws Great, glad it worked for you.

 

For your another question about blank date field:

This is not possible using the view formatting code I provided as it colors the whole row. However, you can override the view formatting by applying column formatting for specific date columns. You can use the JSON similar to this:

 

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
        "background-color": "=if(Number(@currentField) == 0, 'white', '#FDE7E9')"
    }
}

 

You can change the background-color in above JSON as per your requirements. The output of above JSON will look something like below: 

ganeshsanap_0-1624885175147.png

More ways to check if date field is blank: SharePoint JSON formatting: Check if date & time column is blank/empty 


Please click Mark as Best Response & Like 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.

Hi thanks for the quick reply. Sorry not sure if I explained properly, I don't mind the whole row being coloured I just dont want the row to be coloured if for example start date was empty, and only colour the row if it includes a date which has expired. If this is not possible I will use the example you have mentioned with column formatting. Thank you for your help!
best response confirmed by DevHuws (New Contributor)
Solution

@DevHuws Not sure if I understood it correctly. But, either of below JSON codes should work for you: 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
   "additionalRowClass": "=if(([$DueDate] && [$DueDate] <= @now) || ([$EndDate] && [$EndDate] <= @now) || ([$AnyOtherDateColumn] && [$AnyOtherDateColumn] <= @now), 'sp-field-severity--severeWarning', '')"
}

 

OR 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
   "additionalRowClass": "=if((Number([$DueDate]) !=0 && [$DueDate] <= @now) || (Number([$EndDate]) !=0 && [$EndDate] <= @now) || (Number([$AnyOtherDateColumn]) !=0 && [$AnyOtherDateColumn] <= @now), 'sp-field-severity--severeWarning', '')"
}

 


Please click Mark as Best Response & Like 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.