SOLVED

Adding color in column validation

%3CLINGO-SUB%20id%3D%22lingo-sub-1369770%22%20slang%3D%22en-US%22%3EAdding%20color%20in%20column%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1369770%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22x-hidden-focus%22%3EI've%20created%20a%20custom%20list%20in%20SharePoint%20online%20to%20track%20our%20department%20training.%20I%20have%20a%20date%20and%20time%20column%20titled%20%22Expiration%22%20that%20will%20list%20the%20date%20that%20the%20training%2Fcertification%20will%20expire.%20I%20would%20like%20to%20add%20a%20formula%20to%20this%20column%20that%20will%20turn%20orange%20once%20it's%2030%20days%20from%20expiration%20and%20then%20turn%20red%20once%20it's%207%20days%20from%20expiration.%20I've%20searched%20online%20and%20I%20haven't%20found%20a%20formula%20that%20quite%20matches%20what%20I'm%20looking%20to%20do.%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3EI've%20posted%20this%20in%20the%20regular%20SharePoint%20forum%20and%20was%20advised%20to%20post%20it%20here.%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1369996%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20color%20in%20column%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1369996%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659546%22%20target%3D%22_blank%22%3E%40ChiefKeefe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EPlease%20find%20these%20two%20links%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fmicrosoft365.today%2Fmicrosoft-365%2Fcolumn-formatting-to-customize-sharepoint-list%2F%3Futm_source%3Dmicrosoft%26amp%3Butm_medium%3Darticle%26amp%3Butm_campaign%3Dhelp%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmicrosoft365.today%2Fmicrosoft-365%2Fcolumn-formatting-to-customize-sharepoint-list%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%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%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fdeclarative-customization%2Fcolumn-formatting%3C%2FA%3E%3C%2FP%3E%3CP%3EAnd%20let%20me%20know%20if%20you%20need%20more%20help.%3C%2FP%3E%3CP%3EAlireza%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1371459%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20color%20in%20column%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1371459%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F70455%22%20target%3D%22_blank%22%3E%40Alireza%20Rahimifarid%3C%2FA%3E%26nbsp%3BThank%20you.%20I've%20already%20read%20those%20two%20articles%20prior%20to%20reaching%20out%20to%20the%20community.%20The%20part%20I'm%20having%20issues%20with%20is%20integrating%20a%20formula.%20As%20I%20stated%20in%20my%20post%2C%26nbsp%3B%3CSPAN%3EI%20would%20like%20to%20add%20a%20formula%20to%20this%20column%20that%20will%20turn%20orange%20once%20it's%2030%20days%20from%20expiration%20and%20then%20turn%20red%20once%20it's%207%20days%20from%20expiration.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1371641%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20color%20in%20column%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1371641%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659546%22%20target%3D%22_blank%22%3E%40ChiefKeefe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20code%3A%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%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%22color%22%3A%20%22%3Dif(%5B%24Expiration%5D%20%26lt%3B%3D%20%40now%20%2B7%20%2C%20'%23ff0000'%2C%20'')%22%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%7D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1372038%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20color%20in%20column%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1372038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F70455%22%20target%3D%22_blank%22%3E%40Alireza%20Rahimifarid%3C%2FA%3E%26nbsp%3BI%20don't%20think%20that%20will%20give%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659546%22%20target%3D%22_blank%22%3E%40ChiefKeefe%3C%2FA%3E%26nbsp%3Bthe%20correct%20result%2C%20partly%20because%20it%20doesn't%20address%20the%20second%20part%20of%20the%20question%20about%20the%20expiration%20in%20the%20next%2030%20days%2C%20but%20also%20because%20the%20date%20comparison%20needs%20to%20be%20done%20with%20milliseconds.%20So%207%20days%20in%20the%20future%20is%20%3CEM%3E%40now%20%2B%20604800000%3C%2FEM%3E%20and%2030%20days%20is%26nbsp%3B%3CEM%3E%40now%20%2B%202592000000%3C%2FEM%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20Linda%20my%20solution%20is%20as%20follows%20and%20my%20column%20is%20called%20NextReview%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%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%2Fcolumn-formatting.schema.json%22%2C%0A%20%20%22elmType%22%3A%20%22div%22%2C%0A%20%20%22txtContent%22%3A%20%22%40currentField%22%2C%0A%20%20%22style%22%3A%20%7B%0A%20%20%20%20%22color%22%3A%20%22%3Dif(%5B%24NextReview%5D%20%26lt%3B%3D%20%40now%20%2B%20604800000%2C%20'%23d9184b'%2C%20if(%5B%24NextReview%5D%20%26gt%3B%20%40now%2B604800000%20%26amp%3B%26amp%3B%20%5B%24NextReview%5D%20%26lt%3B%3D%20%40now%20%2B%202592000000%2C%20'%23ff9a00'%2C'%23585153')%22%0A%20%20%7D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhich%20gives%20the%20following%20result%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%227Red30Orange.png%22%20style%3D%22width%3A%20973px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F189937i6985FF38D69CC5CC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%227Red30Orange.png%22%20alt%3D%227Red30Orange.png%22%20%2F%3E%3C%2FSPAN%3E%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%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1372063%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20color%20in%20column%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1372063%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659546%22%20target%3D%22_blank%22%3E%40ChiefKeefe%3C%2FA%3E%26nbsp%3Bif%20you%20wanted%20to%20make%20it%20stand%20out%20a%20bit%20more%20then%20you%20could%20apply%20the%20formatting%20with%20a%20background%20color%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%22%24schema%22%3A%20%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fcolumn-formatting.schema.json%22%2C%0A%20%20%22elmType%22%3A%20%22div%22%2C%0A%20%20%22txtContent%22%3A%20%22%40currentField%22%2C%0A%20%20%22style%22%3A%20%7B%0A%20%20%20%20%22background-color%22%3A%20%22%3Dif(%5B%24NextReview%5D%20%26lt%3B%3D%20%40now%20%2B%20604800000%2C%20'%23d9184b'%2C%20if(%5B%24NextReview%5D%20%26gt%3B%20%40now%2B604800000%20%26amp%3B%26amp%3B%20%5B%24NextReview%5D%20%26lt%3B%3D%20%40now%20%2B%202592000000%2C%20'%23ff9a00'%2C'%23dddeee')%22%2C%0A%20%20%20%20%22color%22%3A%20%22white%22%2C%0A%20%20%20%20%22padding-left%22%3A%20%2210px%22%0A%20%20%7D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhich%20gives%20the%20following%20result%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%227Red30Orange-background.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F189940iBD1F1B1D2C3582A2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%227Red30Orange-background.png%22%20alt%3D%227Red30Orange-background.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%20color%3D%22%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1372106%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20color%20in%20column%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1372106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3Band%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F70455%22%20target%3D%22_blank%22%3E%40Alireza%20Rahimifarid%3C%2FA%3E%26nbsp%3B%20Thanks%20so%20much%20for%20the%20assistance!%20I%20really%20appreciate%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I've created a custom list in SharePoint online to track our department training. I have a date and time column titled "Expiration" that will list the date that the training/certification will expire. I would like to add a formula to this column that will turn orange once it's 30 days from expiration and then turn red once it's 7 days from expiration. I've searched online and I haven't found a formula that quite matches what I'm looking to do.

I've posted this in the regular SharePoint forum and was advised to post it here.

Thank you.

6 Replies

@Alireza Rahimifarid Thank you. I've already read those two articles prior to reaching out to the community. The part I'm having issues with is integrating a formula. As I stated in my post, I would like to add a formula to this column that will turn orange once it's 30 days from expiration and then turn red once it's 7 days from expiration. 

@ChiefKeefe 

Here is the code:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if([$Expiration] <= @now +7 , '#ff0000', '')"
}
}

@Alireza Rahimifarid I don't think that will give @ChiefKeefe the correct result, partly because it doesn't address the second part of the question about the expiration in the next 30 days, but also because the date comparison needs to be done with milliseconds. So 7 days in the future is @now + 604800000 and 30 days is @now + 2592000000.

 

So Linda my solution is as follows and my column is called NextReview:

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if([$NextReview] <= @now + 604800000, '#d9184b', if([$NextReview] > @now+604800000 && [$NextReview] <= @now + 2592000000, '#ff9a00','#585153')"
  }
}

 

 

which gives the following result:

 

7Red30Orange.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User 

best response confirmed by ChiefKeefe (New Contributor)
Solution

@ChiefKeefe if you wanted to make it stand out a bit more then you could apply the formatting with a background color:

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$NextReview] <= @now + 604800000, '#d9184b', if([$NextReview] > @now+604800000 && [$NextReview] <= @now + 2592000000, '#ff9a00','#dddeee')",
    "color": "white",
    "padding-left": "10px"
  }
}

 

which gives the following result:

 

7Red30Orange-background.png

 

Rob
Los Gallardos

Microsoft Power Automate Community Super User

@RobElliott and @Alireza Rahimifarid  Thanks so much for the assistance! I really appreciate it!