1

I have a "Completion Date" column and a "Current Step" Column in a list. I am trying to format this "Completion Date" Column so that when the "Current Step" is set to either "Complete" or "Canceled" the "Completion Date" will highlight red and state "Date Needed" if it is empty, and if it is not empty and has a date the column will highlight green. I have no experience with JSON and I tried frankensteining some code together, but it just hasn't been working. Any help would be appreciated!

2 Answers 2

0

Assuming the internal names of your Current Step column is CurrentStep, you can apply a column format to the Completion Date column (Column menu > Column settings > Format this column -> Advanced Mode) using this format:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if(length(@currentField) != 1 && ([$CurrentStep] == 'Complete' || [$CurrentStep] == 'Canceled'), 'Date Needed', toLocaleDateString(@currentField))",
  "attributes": {
    "class": "=if([$CurrentStep] == 'Complete' || [$CurrentStep] == 'Canceled', if(length(@currentField) != 1, 'ms-fontColor-white ms-bgColor-red', 'ms-fontColor-green'), '')"
  }
}

I used the ms-fontColor-* classes for the colors. If you'd rather highlight the background you could use ms-bgColor-* instead (though I'd suggest combining it with ms-fontColor-white in that case). Alternatively, you could set the color and/or background-color directly using style and swapping out the values in the class expression below to either named colors, hex code, or even rgb.

Here's what it looks like (Date provided every other row):

enter image description here


Edit: Updated the format to use a red background (with white font) when Date is needed.

3
  • Thank you! that works, however how can I make it so the date needed cells are shaded red? I tried adding the "sp-css-backgroundColor-Red" to the attributes section, but I couldn't figure out how to add it in the shade the cell as nothing was happening.
    – user103196
    Commented Apr 19, 2022 at 12:40
  • I updated my answer with a solution. If that doesn't meet your needs, just let me know. Good luck! Commented Apr 19, 2022 at 13:57
  • That's perfect, Thank you!
    – user103196
    Commented Apr 19, 2022 at 15:29
0

Please use the below JSON code:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if((Number([$CompletionDate])==0 && ([$CurrentStep]=='Complete' || [$CurrentStep]=='Canceled')),'Date Needed',toLocaleDateString([$CompletionDate]))",
  "style": {
    "color": "=if(([$CurrentStep]=='Complete' || [$CurrentStep]=='Canceled'),if(Number([$CompletionDate])==0,'Red','Green'),'')"
  }
}

enter image description here

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.