“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))"
}

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.

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

For more great content, check out the Resource Centre

About the Author:

Hello and welcome!

My name is Tom and I live in the Czech Republic. I’ve been automating business processes on the Microsoft SharePoint platform for almost 10 years, currently as a freelance consultant.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by “clicking” the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that’s what this blog is about.

Reference:

Riha, T. (2021). Calculate with today’s date in a SharePoint column without daily updates. Available at: https://tomriha.com/calculate-with-todays-date-in-sharepoint-column-without-daily-updates/ [Accessed: 4th March 2021].

Find more great Power Platform content here.