“I’m using JSON formatting to highlight specific SharePoint rows, but it doesn’t work when the date is empty.”
The possibility to format SharePoint views using JSON is a great addition to improve readability. One quick look into the list and users can easily recognise the important items. Is it a fresh item? Make the row green. Was it unchanged for a few months? Make the row orange, or even red. A small piece of code that can make a huge difference.
I already explained the solution in a previous article, but recently I encountered another extension. How do you check if there even is a date? The calculation expects two dates, what if one of them is missing?
Check if date is empty
As you might notice in that article, all the dates are converted into a number for the calculations. If there’s no date it’ll be shown as a 0.
This 0 will break your calculations. When you calculate difference between two numbers and one of them is 0, you won’t get the desired result.
To avoid this problem you might want to check if the date has any value first.
Before you start comparing the actual dates, add another if(…) condition to check whether it contains a date. Since the number for empty date is 0, compare it with 0, e.g.
if(Number([$Next_x0020_date]) > 0, <ifTrue>, <ifFalse>)…and run the calculations only for existing dates (bigger than 0), otherwise do nothing, e.g.
=if(Number([$Next_x0020_date]) > 0, floor((Number(@now)-Number([$Next_x0020_date]))/(1000*60*60*24)),'')
The same logic can be then applied to the whole view. If the date is not empty…
…change the colours of the rows. Otherwise do nothing.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
"additionalRowClass": "=if(Number([$Next_x0020_date]) > 0, if(floor((Number(@now)-Number([$Next_x0020_date]))/(1000*60*60*24)) < Number(30), 'sp-field-severity--good', if(floor((Number(@now)-Number([$Next_x0020_date]))/(1000*60*60*24)) < Number(60), 'sp-field-severity--warning', 'sp-field-severity--blocked')), '')"
}Summary
SharePoint lists (also known as just Lists) are a great tool to display data in a structured way. It has a lot of functionality already included – new/display/edit forms, sorting, filtering, search, etc. But you can still make it a bit better by adding a small piece of a custom code. With a few extra colours it’ll be even easier to identify the SharePoint items requiring some attention – just make sure that you exclude the items with an empty date when using the JSON formatting.
Sorry, not related to this post but I can’t seem to subscribe to your weekly newsletter. It keeps saying: “Please select at least one list.” after I click Sign me up. Thank you!
Hello Harry,
thank you for the message, it’s fixed now.