Creating an indicator for due dates in a sharepoint list

%3CLINGO-SUB%20id%3D%22lingo-sub-3259606%22%20slang%3D%22en-US%22%3ECreating%20an%20indicator%20for%20due%20dates%20in%20a%20sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3259606%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20an%20indicator%20in%20my%20sharepoint%20list%20to%20show%20me%20when%2014%20days%20has%20past%2C%20since%20an%20entry%20into%20the%20list%2C%20but%20only%20if%20another%20column%20is%20at%20a%20certain%20state.%3CBR%20%2F%3E%3CBR%20%2F%3EExample%3A%3CBR%20%2F%3EI%20create%20a%20new%20item%2C%20that%20gets%20the%20created%20date%3A%2001-01-2022%3CBR%20%2F%3EThen%20another%20column%20has%20a%20checkbox%20function%20that%20tells%20me%20if%20my%20task%20has%20been%20completed.%3C%2FP%3E%3CP%3ENow%20what%20I%20want%20is%20to%20format%20the%20date%20column%20to%20get%20a%20red%20background%2C%20if%20no%20checkbox%20has%20been%20set%2C%20and%20it%20has%20been%2014%20days%20since%20the%20entry%20was%20made%20into%20the%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20calculated%20fields%2C%20but%20it%20cannot%20opreate%20with%20TODAY's%20date%2C%20and%20conditional%20formatting%20won't%20let%20me%20chose%20date%2Bx%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3259606%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Lists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3261027%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20indicator%20for%20due%20dates%20in%20a%20sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3261027%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1335579%22%20target%3D%22_blank%22%3E%40Suth_%3C%2FA%3E%26nbsp%3Bthe%20Created%20column%20doesn't%20like%20having%20advanced%20mode%20JSON%20formatting%20applied%20to%20it.%20So%20first%20of%20all%20create%20a%20calculated%20column%20called%20Created%202%20and%20set%20the%20formula%20to%20%3D%5BCreated%5D.%20There%20is%20another%20Yes%2FNo%20column%20for%20the%20Completed%20checkbox.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20format%20that%20Created2%20column%20and%20in%20advanced%20mode%20add%20the%20following%20JSON%20code.%20Note%20that%20JSON%20does%2014%20days%20in%20milliseconds.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%7B%0A%20%20%22%24schema%22%3A%20%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fcolumn-formatting.schema.json%22%2C%0A%20%20%22elmType%22%3A%20%22div%22%2C%0A%20%20%22txtContent%22%3A%20%22%40currentField%22%2C%0A%20%20%22style%22%3A%20%7B%0A%20%20%20%20%22background-color%22%3A%20%22%3Dif(%40currentField%20%26lt%3B%20%40now%20-%201209600000%20%26amp%3B%26amp%3B%20%5B%24Completed%5D%20!%3D%20'Yes'%2C%20'red'%2C%20%20'green')%22%2C%0A%20%20%20%20%22color%22%3A%20%22white%22%0A%20%20%7D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello

 

I am trying to create an indicator in my sharepoint list to show me when 14 days has past, since an entry into the list, but only if another column is at a certain state.

Example:
I create a new item, that gets the created date: 01-01-2022
Then another column has a checkbox function that tells me if my task has been completed.

Now what I want is to format the date column to get a red background, if no checkbox has been set, and it has been 14 days since the entry was made into the list.

 

I have tried calculated fields, but it cannot opreate with TODAY's date, and conditional formatting won't let me chose date+x

 

Any suggestions?

1 Reply

@Suth_ the Created column doesn't like having advanced mode JSON formatting applied to it. So first of all create a calculated column called Created 2 and set the formula to =[Created]. There is another Yes/No column for the Completed checkbox.

 

Then format that Created2 column and in advanced mode add the following JSON code. Note that JSON does 14 days in milliseconds.

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField < @now - 1209600000 && [$Completed] != 'Yes', 'red',  'green')",
    "color": "white"
  }
}

 

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)