SharePoint List: Conditional Formatting on Date field using calculation or calculated column

%3CLINGO-SUB%20id%3D%22lingo-sub-2835928%22%20slang%3D%22en-US%22%3ESharePoint%20List%3A%20Conditional%20Formatting%20on%20Date%20field%20using%20calculation%20or%20calculated%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2835928%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20a%20novice%20and%20hoping%20someone%20can%20help%20me%20out.%20First%20thing%2C%20I%20am%20not%20good%20with%20any%20code%20languages%20so%20please%20be%20kind%20in%20your%20responses%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20SharePoint%20list%20which%20has%20a%20date%20column%20called%20%22Doc%20Request%20Date%22%3A%3C%2FP%3E%3CUL%3E%3CLI%3EI%20need%20this%20date%20column%20to%20highlight%20to%20alert%20the%20user%20if%20the%20document%20sent%20has%20expired.%3C%2FLI%3E%3CLI%3EDocuments%20expire%2035%20calendar%20days%20after%20the%20Doc%20Request%20Date.%3C%2FLI%3E%3CLI%3EAdditionally%2C%20there%20is%20a%20date%20complete%20column%2C%20which%20if%20containing%20a%20date%2C%20means%20the%20issue%20is%20closed%20and%20therefore%20no%20alerts%20are%20required.%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20would%20ideally%20like%3A%3C%2FP%3E%3CUL%3E%3CLI%3EIf%20%22Doc%20Request%20Date%22%26nbsp%3B%20%2B35%20days%20is%20before%20today%20and%20%22Complete%20Date%22%20is%20empty%2C%20then%20background%20green%3C%2FLI%3E%3CLI%3EIf%26nbsp%3B%22Doc%20Request%20Date%22%20%2B35%20days%20is%20equal%20to%20today%20and%20%22Complete%20Date%22%20is%20empty%2C%20then%20background%20yellow%3C%2FLI%3E%3CLI%3EIf%20%22Doc%20Request%20Date%22%20%2B35%20days%26nbsp%3B%20is%20after%20today%20and%20%22Complete%20Date%22%20is%20empty%2C%20then%20background%20red%3C%2FLI%3E%3CLI%3EIf%20%22Complete%20Date%22%20has%20a%20value%2C%20then%20%22Doc%20Request%20Date%22%20background%20is%20white%3C%2FLI%3E%3C%2FUL%3E%3CP%3EHow%20would%20I%20go%20about%20doing%20this%3F%20Is%20it%20possible%3F%20I%20tried%20creating%20a%20few%20calculated%20columns%20to%20base%20the%20conditional%20formatting%20on%20before%20I%20realised%20that%20the%20conditional%20formatting%20doesn't%20work%20for%20calculated%20columns!%20I%20think%20I%20need%20to%20do%20this%20in%20the%20advanced%20JSON%20editor%2C%20but%20like%20I%20said%2C%20I%20am%20useless%20at%20any%20form%20of%20code%20language.%20ANY%20help%20would%20be%20greatly%20appreciated!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2835928%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi, I am a novice and hoping someone can help me out. First thing, I am not good with any code languages so please be kind in your responses :)

 

I have a SharePoint list which has a date column called "Doc Request Date":

  • I need this date column to highlight to alert the user if the document sent has expired.
  • Documents expire 35 calendar days after the Doc Request Date.
  • Additionally, there is a date complete column, which if containing a date, means the issue is closed and therefore no alerts are required. 

 

What I would ideally like:

  • If "Doc Request Date"  +35 days is before today and "Complete Date" is empty, then background green
  • If "Doc Request Date" +35 days is equal to today and "Complete Date" is empty, then background yellow
  • If "Doc Request Date" +35 days  is after today and "Complete Date" is empty, then background red
  • If "Complete Date" has a value, then "Doc Request Date" background is white

How would I go about doing this? Is it possible? I tried creating a few calculated columns to base the conditional formatting on before I realised that the conditional formatting doesn't work for calculated columns! I think I need to do this in the advanced JSON editor, but like I said, I am useless at any form of code language. ANY help would be greatly appreciated!!

0 Replies