Forum Discussion
SharePoint List formatting a Date column
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.
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.
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" } }Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)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?
sandbox
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"
}
}
