Calculate with today’s date in a SharePoint column without daily updates

“How can I display number of days between today’s date and another date in a SharePoint column without Power Automate daily updates?”


Calculating a number of days between a date and today’s date is not simple in SharePoint. You probably use calculated column for that, but calculated column can’t work with today’s date directly. It offers today() function, but the today() date does not update automatically. It’s updated only when the item is updated, otherwise it keeps the original value. If you don’t update the item for a month, the today() function will keep the same date for a month. That’s not really an acceptable solution.

Other solution is to add another column for today’s date and update the column daily with Power Automate. A scheduled flow that would run daily and update the today’s date for all items. Once you have both the dates in a column, you can easily use them in a calculation. The problem can be the daily updates. All items are updated every day, if you keep version in the list, you’ll get 365 version every year just to update the date. If you’ve got a flow with trigger on item modifications on that list, you must be careful. If users have any alerts on the list, they’ll receive a lots of emails. I’d say there’re too many potential problems with this approach.

Use JSON column formatting

Since the goal is to display a number of days between two dates, you don’t really need a calculated column for the calculation. You can use any column and format it with a JSON to display the result of the calculation. It’s the same approach as when building a calculated hyperlink.

JSON allows you to use @now, a placeholder for today’s date. Unlike the today() function in calculated column formula, @now will give you always today’s date. That allows you to calculate the difference in days between today and the other date. The example below will calculate number of days since the SharePoint item was created (using the Created column with the [$Created] placeholder).

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=floor((Number(@now)-Number([$Created]))/(1000*60*60*24))"
}
SharePoint calculation using today's date and another date column

Image note: ‘Calculated’ column is using the today() function. ‘New column’ is a Single line of text column with the JSON formatting. The screenshot is from February 21, 2021.

To break down the calculation into pieces:

  • Number(…) will convert a date into a number in milliseconds
  • Number(@now)-Number([$Created]) will take today’s date in milliseconds and subtract from it Created date in milliseconds
  • (1000*60*60*24) will calculate duration of a day in milliseconds: 1000 milliseconds * 60 seconds * 60 minutes * 24 hours
  • floor((Number(@now)-Number([$Created]))/(1000*60*60*24)) will take the difference between dates, divide it by duration of a day, and round the final number down to a full number

You can use this formatting on any SharePoint column. It’ll suppress the value in the column and display the result of the calculation instead.

Summary

I consider JSON formatting on SharePoint column a much better replacement for Calculated columns. It gives you so many possibilities to change the looks and functionality on the SP views, just take a look on the Microsoft article.

It allows you to do calculations as shown in this post, you can use it to build hyperlinks, hide empty links, and much more.

I should just mention that JSON formatting will work only in Modern SP. If you’re still using Classic SP you might need to switch it, but you’ll do it anyway sometime in the future, why not now.

And to move the functionality one step further, you could add also a reminder flow.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

29 Comments

Add a Comment

Your email address will not be published. Required fields are marked *