SOLVED

Conditional Format SharePoint List Due Dates with three conditions - RAG

%3CLINGO-SUB%20id%3D%22lingo-sub-1586928%22%20slang%3D%22en-US%22%3EConditional%20Format%20SharePoint%20List%20Due%20Dates%20with%20three%20conditions%20-%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586928%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20tried%20searching%20for%20this%20seemingly%20everywhere%20and%20although%20there's%20lots%20of%20people%20with%20similar%20problems%20I've%20not%20found%20someone%20with%20this%20exact%20one%20and%20therefore%20this%20answer.%20Apologies%20if%20it%20is%20a%20repeated%20question%20but%20I%20just%20can't%20find%20a%20straightforward%20answer%20anywhere%20else.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20due%20date%20column%20in%20my%20Sharepoint%20list.%20I%20want%20this%20to%20highlight%20the%20cell%20in%20the%20following%20ways%3A%3CBR%20%2F%3ERed%20if%20due%20today%20or%20overdue%3C%2FP%3E%3CP%3EAmber%20if%20it's%20due%20within%2030%20days%3C%2FP%3E%3CP%3EGreen%20if%20due%2030%2B%20days%20away.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMost%20json%20codes%20I%20find%20either%20only%20have%20two%20variables%20(red%20or%20green%2Foverdue%20or%20in%20date)%20and%20as%20far%20as%20I%20can%20tell%20the%20automatic%20design%20mode%20conditional%20formatting%20only%20refers%20to%20'today'%20rather%20than%20'within%20a%20certain%20number%20of%20days%20to%20today'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20any%20code%20which%20does%20the%20above%20I%20can%20use%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1586928%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional 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?

4 Replies
best response confirmed by misce (Occasional 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?