“Is it possible to not only change colour in a specific cell, but to highlight the whole SharePoint row when using calculated date field?”
There’s an older article explaining how to use JSON formatting to do calculations with today’s date and a post on formatting the column based on the result. But what if you don’t want to highlight only the column, but the whole row? To show the whole item in red for low numbers and green for high numbers? How do you achieve such formatting?
Start with the View format
SharePoint allows you not only to format column, but you can format the whole view. Start from any column in the list to get to the view formatting.
It’ll show you two options – ‘Alternating row styles’ and ‘Conditional formatting’. Use the latter one, the conditional formatting and click on ‘Manage rules’.
Prepare a dummy rule for every option you want to use. For example, if you want 3 different colours based on the number of days, create 3 rules using some dummy conditions.
Once you have a condition for all the colours you want to use, switch to the ‘Advanced mode’ and get the JSON formatting.
Modify the conditions in the JSON
Copy paste it into some code editor (I always recommend Visual Studio Code), it’ll look as below. You can see that it contains always the condition (red) and the format it should apply (green).
Since all the conditions were “dummy” conditions, it’s now up to you to replace them. Take the calculation from the original article and use it to calculate the date difference.
=floor((Number([$<dateColumnName>])-Number(@now))/(1000*60*60*24)Note: <…> is a placeholder, replace it including the < and >.
Replace also the operands and the value to compare it with, e.g.
Use the updated JSON
Copy/paste the updated code back into the View formatting Advanced mode. Click ‘Save’ and you’re done, you just formatted the whole SharePoint row based on the calculated date difference.
Summary
It’s possible to highlight the whole SharePoint row based on a date in a calculated column, and it’s not even that complicated. If you use the SharePoint formatting designer it’ll do most of the work for you, all that’s left is to replace the conditions with the calculation.