How to format due date based on remaining working days

Claudio Don 20 Reputation points
2023-07-18T12:32:59.7766667+00:00

Hello, maybe this kind of question has been discussed in this forum, but I didn't find what I need (also trying to modify the existing code)

I have a task table in MS Teams (SharePoint) and I want to color the background of "Due Date" the cell based on this rule:

  • if remain >4 days to the due date leave the format as standard
  • if remain 4 days to the due date (starting from current day), the background should be yellow and add 4 days next to the date
  • if remain 3 days to the due date (starting from current day), the background should be light orange and add 3 days next to the date
  • if remain 2 days to the due date (starting from current day), the background should be light red and add 2 days next to the date
  • if remain 1 days to the due date (starting from current day), the background should be red and add 1 days next to the date
  • if remain 0 days to the due date (starting from current day), the background should be dark red and add 0 days next to the date
  • if pass 1 or more than 1 days to the due date (starting from current day), the background should be dark red and write OVERDUE

Please if someone can help me I will be grateful

Thanks in advance

Claudio

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
11,206 questions
{count} votes

Accepted answer
  1. Zehui Yao_MSFT 5,871 Reputation points
    2023-07-19T10:30:43.5166667+00:00

    Hi Claudio Don,

    It's a pleasure to be able to help you.

    Here are the results we've tested so far. You can refer to the following JSON content to see if it helps you.

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
      "elmType": "div",
      "txtContent": "=if(@currentField > @now + 4 * 86400000, '@currentField', if(@currentField > @now + 3 * 86400000, @currentField + 4 * 86400000, if(@currentField > @now + 2 * 86400000, @currentField + 3 * 86400000, if(@currentField > @now + 1 * 86400000, @currentField + 2 * 86400000, if(@currentField > @now, @currentField + 1 * 86400000, if(@currentField - @now < 0, 'OVERDUE', @currentField)))))",
      "style": {
        "background-color": "=if([$DueDate] > @now + 4 * 86400000, '', if([$DueDate] > @now + 3 * 86400000, 'yellow', if([$DueDate] > @now + 2 * 86400000, '#FED8B1', if([$DueDate] > @now + 1 * 86400000, '#FFCCCB', if([$DueDate] > @now, 'red', if([$DueDate] - @now < 86400000, '#8B0000', ''))))))"
      }
    }
    

    User's image


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.