Forum Discussion

suhail_84's avatar
suhail_84
Copper Contributor
Apr 01, 2022

calculated column for past due item

Hello Everyone, 

 

How can I create a calculated column for past due items in a SharePoint list? I have tried this formula, but it isn't working. 

 

=IF([Due Date]>"TODAY","NOT DUE",IF([Due Date]<"TODAY","PAST DUE"))

 

Please assist. 

  • RobElliott's avatar
    RobElliott
    Silver Contributor

    suhail_84 you can't use Today in a calculated column. And just adding Today into another column won't work because Today() doesn't update automatically. But there's another solution you might wish to consider which is to color code the Due column depending on if the due date is after today or before today. This is because JSON formatting uses @now and will always evaluate against today's date.

     

    Format the column in advanced mode and add the following:

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

     

     

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

    • suhail_84's avatar
      suhail_84
      Copper Contributor
      Thanks, this works ! However, is there any way I can also change the status?
      • RobElliott's avatar
        RobElliott
        Silver Contributor

        suhail_84 to display the status you can use JSON advanced column formatting again, although this doesn't save the status to the list it just displays it:

         

        {
          "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
          "elmType": "div",
          "txtContent": "=if([$Due] > @now, 'NOT DUE','PAST DUE')"
        }

         

         

        If you wanted to save the status to the list itself so you could do other reporting on it then I would recommend building a simple recurrence schedule flow in Power Automate that runs once a day to update the list: if the due date is greater than utcNow() then set the status column to Not Due, otherwise set it to Past Due.

         

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

Resources