SOLVED

Conditional Format SharePoint List Due Dates with three conditions - RAG

Copper Contributor

I've tried searching for this seemingly everywhere and although there's lots of people with similar problems I've not found someone with this exact one and therefore this answer. Apologies if it is a repeated question but I just can't find a straightforward answer anywhere else.

I have a due date column in my Sharepoint list. I want this to highlight the cell in the following ways:
Red if due today or overdue

Amber if it's due within 30 days

Green if due 30+ days away. 

 

Most json codes I find either only have two variables (red or green/overdue or in date) and as far as I can tell the automatic design mode conditional formatting only refers to 'today' rather than 'within a certain number of days to today'.

 

Does anyone have any code which does the above I can use?

6 Replies
best response confirmed by misce (Copper Contributor)
Solution

@misce I was recently looking for a similar resource myself. If you are still in need, this code should work for you. If you don't like the exact RAG colors, you can easily replace the them with a different HEX (here is a great site for picking colors: Color Picker Tool - Hex Colors)

 

{
    "elmType""div",
    "txtContent""@currentField",
    "style": {
        "background-color""=if(Number(@currentField) == 0, '', if(@currentField >= @now + 2592000000, '#AFF8C9', if(@currentField >= @now + 86400000, '#EEBB2F', if(@currentField <= @now, '#F06C47',''))))"
    }
}

@TKLR_83 Thank you! This worked for me! 

Unfortunately I can't get this to work, but I'm going to mark it as correct as it has for someone else so it must just be something I'm doing wrong!

A couple of questions I guess, 1) do I replace @currentField with the name of the column or can I just leave it as that?

I think on second thoughts that my problem may be that the column is a Single Line of Text column, because it's getting data from a PowerApp that was easier at the time, so I'm not sure if it will be possible to do what I wanted anyway.

Thanks for your response!
This is exatly what I needed thank you! Would you know how I'd also make the data appear in bold, as well as having the background colours appear?

@SteCoxy add additional items to the Style section based on your desired CSS

@SteCoxy You can use font-weight parameter for showing text in bold. 

 

Example: 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(Number(@currentField) == 0, '', if(@currentField >= @now + 2592000000, '#AFF8C9', if(@currentField >= @now + 86400000, '#EEBB2F', if(@currentField <= @now, '#F06C47',''))))",
    "font-weight": "bold"
  }
}

 

You can also use the similar expression as used for background-color if you want to show the bold text conditionally.


Please consider giving a Like if my post helped you in any way.

1 best response

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

@misce I was recently looking for a similar resource myself. If you are still in need, this code should work for you. If you don't like the exact RAG colors, you can easily replace the them with a different HEX (here is a great site for picking colors: Color Picker Tool - Hex Colors)

 

{
    "elmType""div",
    "txtContent""@currentField",
    "style": {
        "background-color""=if(Number(@currentField) == 0, '', if(@currentField >= @now + 2592000000, '#AFF8C9', if(@currentField >= @now + 86400000, '#EEBB2F', if(@currentField <= @now, '#F06C47',''))))"
    }
}

View solution in original post