Forum Discussion
calculated column for past due item
suhail_84 you can't use Today in a calculated column. And just adding Today into another column won't work because Today() doesn't update automatically. But there's another solution you might wish to consider which is to color code the Due column depending on if the due date is after today or before today. This is because JSON formatting uses @now and will always evaluate against today's date.
Format the column in advanced mode and add the following:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField > @now, 'green', 'red')",
"color": "white"
}
}
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
- Thanks, this works ! However, is there any way I can also change the status?
suhail_84 to display the status you can use JSON advanced column formatting again, although this doesn't save the status to the list it just displays it:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json", "elmType": "div", "txtContent": "=if([$Due] > @now, 'NOT DUE','PAST DUE')" }If you wanted to save the status to the list itself so you could do other reporting on it then I would recommend building a simple recurrence schedule flow in Power Automate that runs once a day to update the list: if the due date is greater than utcNow() then set the status column to Not Due, otherwise set it to Past Due.
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)RobElliott when I use your JSON advanced column formatting code above my status column populates all rows with “Past Due.”
I need the third row to say “Not Due” since that date is still in the future. What am I doing wrong?
