This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(86.4, 84%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETB%3C/text%3E%3C/svg%3E)
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:
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:
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:
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!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(57.599999999999994, 33%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHX%3C/text%3E%3C/svg%3E)
Hi @Treb Bert ,
I'm not sure I understand what you mean. So I need to confirm something with you first.
You'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). Review Date or Closed Date either of which is already beyond the current date shows the Review Date in red right? If it's a holiday it shows red until the first business day after the holiday ends? Perhaps you could provide more detailed data to make it easier for me to understand your rules.
Can you give me your formula in excel as a reference? Thank you for your understanding!
Hi,
Apologies for the late reply. Our basis would be the "Review Date", if review date is beyond 3 days, whole row of cells will be highlighted as red. "Closed Date" is the basis for the red highlight to be gone if a transaction was processed within 2 banking days from the said "Review Date". This exclude weekends from the count.
Example of this Date:
Our basis would for the date will be the Review Date and it will only highlight as red if it is beyond 3 days from current date, including weekends. Highlight will be gone if "Closed Date" is processed within 2 days from the current assigned date from Review Date.
With regards to the holidays, I usually process it manually as I don't know how to process it :(. If you could help me with sharepoint to exclude a particular date, that would be a huge help for me. Anyhow, I usually do this to my excel conditional formatting:
=AND(A1>TODAY()+3, WEEKDAY(A1,2)<=5)
Thank you so much for the help!
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','')"
}
Hi, thank you for the help. Currently, the provided json formula does not work:(((
This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
I've already inputted it under JSOn formatting and put it under list. After that, the cell will still not highlight even if I am inputting a future date. Today is May 6 and if I input May 13, whole data will not highlight as red. Note that I've already inputted formula for CALweekday on a calculated column
Hi, I apologize for the complexity of your situation, I tried to understand and write formulas but never seemed to understand your exact conditions with any precision. Please check this formula to see if it meets your requirements (the entire line turns red if the current date is more than two business days after the ReviewDate):
=if([$CALweekday] <= @now,'sp-field-severity--severeWarning','')
If this doesn't fulfill the conditions, you can only be bothered to preferably list each case in an excel sheet and mark if it needs to be red and exactly why it is red. I'm editing the formulas precisely for you, thank you for your understanding!
Thaank youuu! Will I be inputting this under the calculated column or add it to JSON list?
You need to create the calculated column (CALweekday) and then put this code(
=if([$CALweekday] <= @now,'sp-field-severity--severeWarning','')
) into the json.
Thank you so much for helping me but currently, I have this error when inputting the code under JSON for CALweekday. Tried tweaking it but unable to input it as such:
Current thing that is happening wherein there is no red highlight even if review date is beyond.
Your help is so much appreciated. If we have resolved this, I'd like to make a quick summary through the answers :))) Thank you agaain!
You need to format the current view using json, not the CALweekday column.
I have a doubt, if today is 5.6 and enter 5.13 as a future date, why does it show red?
Thank you, this is now working. My only problem is for it to not highlight as red if I inputted "Closed Date" within 2 days from the Review Date. This would be the example:
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.
Awww, is there a way for me to create a column that modifies weekday in Sharepoint? Like, =IF(WEEKDAY([Process Date])=2,[Process Date]+3,(IF(WEEKDAY([Process Date])=3,[Process Date]+3,(IF(WEEKDAY([Process Date])=1,[Process Date]+3+1,[Process Date]+3+2))))), then make it as a JsOn formula?
Hi, Do you mean to create a separate column Exclude weekends and then use json to highlight the whole row as red according to the condition. or do you want to convert the formula you provided to be used in json?
Yes use JSON to highlight the whole row as red according to a condition. It will be much appreciated :)))
If I understand you correctly, you are trying to follow the method in this link, only the json conditions are modified right?
Yes, but I would need a new particular conditional format on the mentioned new thread I've created lately. I followed it but I would need to have it highlight as red only if beyond 3 days (from Review Date) and will not highlight as red if close date is within 2 days from review date and if it is not yet beyond the 3 day period
Hi,
Using the following view json format, the result is shown below:
{
"$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','')"
}
Duuuude! Thank you so much for the help! It already worked!!! 😁 You are the best!