Forum Discussion
Conditional Formatting in Sharepoint List based on expiry date within 30 days
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.
AmarTrivedi format the view with JSON similar to this:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json", "additionalRowClass": "=if([$Arrival] < addDays(@now(),10), 'ms-bgColor-red', if([$Arrival] >= @now() && [$Arrival] <= addDays(@now(),30), 'ms-bgColor-yellow', 'ms-bgColor-green')) + ' ms-fontColor-white'" }Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
- 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.
SOE_Tech if the current field is empty then use =if(Number(@currentField)==0,'', if.....
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "font-weight": "bold", "background-color": "=if(Number(@currentField) == 0, '', if(@currentField <= addDays(@now, 30), 'red', if(@currentField > addDays(@now, 30) && @currentField < addDays(@now, 60), 'orange', '#006666')))", "color": "white" } }Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
