SOLVED

SharePoint List formatting a Date column

Copper Contributor

I have a SharePoint List. Two of the columns are "Due Date" and "Status". The Due Date column is obviously, populate with dates, with times turned off. The Status field is a choice field, formatted in pills and contains Queued, Work in Progress, On-Hold, Completed and Cancelled. If the Date is prior to the current day, meaning past due, AND the status is NOT Cancelled or Completed, I want to format the Due Date text. I would like it to be Centered, Bold and Red. I would also like to make it a slightly larger text, if possible, using font size like 8, 10, 12 ... so I can easily alter the code. I would like to keep the existing background color and also use hexadecimal code, #FF0000, to represent the red color, so I can easily tweak the color if needed.

4 Replies

@johncrouse62 format the Due Date column in advanced mode with the following JSON:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if(@currentField < @now && [$Status] != 'Completed' && [$Status] != 'Cancelled', '#FF0000','')",
    "font-size": "=if(@currentField < @now && [$Status] != 'Completed' && [$Status] != 'Cancelled', '13pt', '11pt')",
    "font-weight": "=if(@currentField < @now && [$Status] != 'Completed' && [$Status] != 'Cancelled', 'bold', 'normal')",
    "justify-content": "center"
  }
}

 

0-SPList-Color.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

best response confirmed by johncrouse62 (Copper Contributor)
Solution

johncrouse62_0-1720636826637.png

 

@Rob_Elliott

Thanks a TON! Very close, but the Cancelled and Completed items are still showing Past Dues. I need them to NOT have the code applied to them, just remain as the default settings. I don't care if they are highlighted as Past Due. Also, the entire column had its font size increased, not just the bold red ones. The non-Red ones should remain like the text in the column to the left, which is my default.

Rob, I have the code working in my sandbox but not in my live environment. Kinda tells me it's something in my Teams / SharePoint List. As you can see in the first screenie, my sandbox, there are 3 rows that are a day late and the Cancelled and Complete statuses do not show past due. That is what I am after. The second screenie in my live environment. No working, for some reason. The code is at the bottom. I do realize the first screenie says "Complete" and the second says "Completed". I have accounted for that in the code. Any Thoughts?

 

johncrouse62_0-1720666578018.png

sandbox

 

johncrouse62_1-1720666750074.png

live environment

 


{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if(@currentField < @now && [$Status] != 'Complete' && [$Status] != 'Cancelled', '#FF0000','')",
"font-size": "=if(@currentField < @now && [$Status] != 'Complete' && [$Status] != 'Cancelled', '13pt', '')",
"font-weight": "=if(@currentField < @now && [$Status] != 'Complete' && [$Status] != 'Cancelled', 'bold', '')",
"justify-content": "center"
}
}

This is now working with Rob's original code he provided. Thank you Rob!

1 best response

Accepted Solutions
best response confirmed by johncrouse62 (Copper Contributor)
Solution

johncrouse62_0-1720636826637.png

 

@Rob_Elliott

Thanks a TON! Very close, but the Cancelled and Completed items are still showing Past Dues. I need them to NOT have the code applied to them, just remain as the default settings. I don't care if they are highlighted as Past Due. Also, the entire column had its font size increased, not just the bold red ones. The non-Red ones should remain like the text in the column to the left, which is my default.

View solution in original post