Sharepoint JSON Formatting for dates that is past due for 3 days from the current working date

Treb Bert 60 Reputation points
2024-05-02T05:53:15.1433333+00:00

Hi,

For your assistance, please. I'm having another problem on my other Sharepoint list that I'm creating.

I'd need to have a particular cell to be highlighted as red if it is already beyond the current date (weekends and holidays should not be included). Please refer to the screenshot of Excel example:

Let's assume that today is May 6, 2024:

There will be no red highlight as it is currently within the date. This includes the weekends of Saturday and Sunday:

User's image

There will be red highlight as it is already beyond the date of May 6, 2024. It is already highlighted as May 6 is already the 3rd date and is beyond the date:

User's image

There will be no red highlight even if closed date is empty as it is still within the due date. May 6 is only the second day:

User's image To summarize it, closed date and review date are both necessary cells as they will be interacting with each other. I've already managed to do it in Excel Conditional Formatting but I'm having a hard time to do it in JSON conditional formatting via Sharepoint.

Your help will be much appreciated. Thank you!

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

Accepted answer
  1. Xyza Xue_MSFT 25,466 Reputation points Microsoft Vendor
    2024-05-03T08:47:29.8466667+00:00

    Hi @Treb Bert ,

    Please check if this json is compliant:

    { 
    "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
    "additionalRowClass": "=if(([$ReviewDate] <= @now - 3)&&([$ClosedDate] >= [$CALweekday]),'sp-field-severity--severeWarning','')"
    }
    
    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Xyza Xue_MSFT 25,466 Reputation points Microsoft Vendor
    2024-05-03T06:36:06.07+00:00

    Hi @Treb Bert ,

    Thanks for the detailed explanation and time, after my research, your requirement cannot be fully realized by sharepoint JSON Formatting. Because as far as I know, there is no WEEKDAY method in Sharepoint JSON code. So we are unable to do this, thanks for your understanding and support!


    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.


  2. Xyza Xue_MSFT 25,466 Reputation points Microsoft Vendor
    2024-05-07T02:27:29.4033333+00:00

    Hi,

    Using the following view json format, the result is shown below:

    User's image

    {
     "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
    "additionalRowClass": "=if(([$CALweekday] <= @now && ([$ClosedDate] >= [$CALweekday]))||((Number([$ClosedDate]) == 0)&&([$CALweekday] <= @now)),'sp-field-severity--severeWarning','')"
    }
    

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.