How to calculate days late based on todays date and the due date of an item

Question

How to calculate days late based on todays date and the due date of an item

Answer 1

Hi @Dave Cleary 

Welcome to the Microsoft Q&A forum.  

Thank you for reaching out, and I know how important it is to have accurate “days late” tracking, especially when you're relying on SharePoint to keep things running smoothly. I’m happy to walk you through the best way to achieve this. 

In SharePoint, the most reliable way to calculate days late (based on today’s date and your item’s due date) is to subtract the due date from today’s date.  

If today is after the due date, the result will be a positive number representing how many days the item is overdue. If today is the same as or before the due date, the value will be zero or negative, meaning it isn’t late.  

Depending on how you want this to behave in your SharePoint list, you have a few options: 

Option 1: Calculated column  

You can use a calculated column with a formula, but SharePoint’s calculated columns do not refresh daily because TODAY() is not evaluated dynamically. 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"     
	} 
} 

Please note: This script is just a template. It will need to be customized based on your specific site structure, content, and business requirements. 

Option 2: Use JSON column formatting 

If you’d like the value to update automatically every day, JSON formatting is the most effective method. It uses @now, which refreshes dynamically whenever the list is opened. Microsoft’s community guidance highlights that JSON can calculate date differences dynamically using Number(@now) and Number([Due Date]). 

Note: Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.  

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.    

I look forward to your thoughts on this. 

---

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.