Forum Discussion

Suth_'s avatar
Suth_
Copper Contributor
Mar 17, 2022

Creating an indicator for due dates in a sharepoint list

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?

microsoft lists
SharePoint Online

1 Reply

  • RobElliott's avatar
    RobElliott
    Silver Contributor
    Mar 18, 2022

    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)

Resources