Forum Discussion
Conditional Formatting in Sharepoint List based on expiry date within 30 days
I currently have a list which shows training. the box shades red when the training has expired and is green when it is active. I would like it to go Orange when the training is going to expire in the next 60 days. I was wondering if someone can help me write in the advance setting mode? Thank you
10 Replies
I am trying to do similar but based on last modified... upto 7 days green, up to 14 days amber and then 30 days red. But I cannot seem to crack it..
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField >= @now-604800000, 'green', if(@currentField > @now-1209600000 && @currentField < @now-2592000000, 'amber', 'red')",
"color": "white"
}
}ganeshsanap
I am trying to do similar to but flag when modified after 7 days (green), 14 days (amber) and 30 days (red). I cannot seem to work out the code as I am a real noob... what am I doing wrong?
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField >= @now-604800000, 'green', if(@currentField > @now-1209600000 && @currentField <= @now-2592000000, 'amber', 'red')",
"color": "white"
}
}Beastie1211 Not sure about all your requirements, but try this and see if it works for you:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "background-color": "=if(@currentField >= @now-604800000, 'green', if(@currentField > @now-1209600000 && @currentField <= @now-2592000000, 'amber', 'red'))", "color": "white" } }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.
SLCLARKE23 dates in JSON use milliseconds. So 60 days is 5184000000 milliseconds:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "background-color": "=if(@currentField > @now+5184000000, 'green', if(@currentField > @now && @currentField < @now+5184000000, 'orange', 'red')", "color": "white" } }Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)RobElliott This will work for particular field. But can you please help me with if we want it to be applied to whole view?
AmarTrivedi You can use SharePoint JSON view formatting for your requirements. Use JSON like:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json", "additionalRowClass": "=if(Number([$MyDateColumn]) == 0, '', if([$MyDateColumn] <= addDays(@now, 30), 'sp-field-severity--blocked', if([$MyDateColumn] > addDays(@now, 30) && [$MyDateColumn] < addDays(@now, 60), 'sp-field-severity--low', 'sp-field-severity--good')))" }Where [$MyDateColumn] is an internal name of your date & time column in SharePoint list in this format: [$InternalNameOfColumn]. You can get the internal name of your SharePoint list columns by following this article: How to find the Internal name of columns in SharePoint Online?
Also, you can change the classes like sp-field-severity--blocked in above JSON as per your requirements. You can find some useful classes at (Thanks to Denis Molodtsov😞 SharePoint Online CSS Classes
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.
- Can i be so bold and ask how to to "nothing" if the current field is empty?
With the current JSON the empty fields get red as wellYou can find different/multiple ways to check if SharePoint date & time column is empty or not using JSON formatting at: 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.
