“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.
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.
UPDATE: there’s now also a post dedicated to formatting the result.
Very useful post Tom thank you. Can you apply additional conditional formatting (e.g. severity colors) to what is now essential just a display value rather than an actual field value?
Hello Mike,
yes, you can, but since it’s just a display value you should use the same calculation in the condition, e.g. to use red color for date difference bigger than 10:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=floor((Number(@now)-Number([$Created]))/(1000*60*60*24))",
"style": {
"color": "=if(floor((Number(@now)-Number([$Created]))/(1000*60*60*24)) > 10, '#ff0000', '')"
}
}
Thanks for the guidance! It works well in sharepoint. However, with Power Automate (Select > Create html Table > Compose), the display value (day difference) does not show up in email. In othter words, the display value (day difference) will be having missing field in email. Are you able to advise?
Hello Kent,
JSON column formatting is on user side only (calculated in the browser), the value is not stored in the list and therefore can’t be used in the flow. To use the value in a flow you’ll need to recreate the calculation. The expression below will calculate the difference between today and a date in a flow, don’t forget to replace the whole placeholder [dateDynamicContent] with your date.
div(sub(ticks(utcNow()),ticks([dateDynamicContent]),864000000000)Thanks Tom for the prompt response! I applied the expression above and it will be generating the day difference in Apply To Each (for each row item in Sharepoint). Next. how am i going to insert the output (day difference generated by Apply To Each) in Map in Select operation?
I attempted with Append to String/Array Variable but it does not work. It will display 1511 /[15,1,1] respectively. The output is lumped together instead. Sidenote: first, second and third day difference is 15, 1 and 1 day respectively.
Hello Kent,
you don’t need any preprocessing, use the expression directly in the ‘Select’ action. Enter it into the right column in the same way as you’d enter any other dynamic content coming from SharePoint.
Tom, it works well indeed! I appreciate your guidance to clear up my doubt! Thanks again!
Hi, thanks for the info! I only need the column to display today’s date with hours, minutes and second and, without substracting anything. I used your Json but I took out the whole formula except @now, is there another expression to get the date without the time? Thanks!
sorry I meant:
“… I only need the column to display today’s date WITHOUT hours, minutes and second and, without substracting anything…”
Hello Mauro,
I think that the easiest way would be to use toLocaleDateString(…) function:
“txtContent”: “=toLocaleDateString(@now)”
Hi this code works great, I was wondering if I would be able to calculate the days to go, based upon the expiry date (date field) minus [today] rather than date created minus [today]?
Thank you
Hello Jean-Luc,
you can access the expiry date date in the same way as the created date [$Created], just replace the ‘Created’ with the other column name.
Is there any way to sort the “New Column”? Because it’s a text field, the normal numerical sorting isn’t working. Filter doesn’t work either. Hoping there is a workaround – thanks!
Hello Christine,
you can’t sort by the values calculated in the JSON column, the calculation takes place in user’s browser and it’s only shown to the user, it’s not stored in the list.
Hi, how do I retrieve data from that column?
Hello Ali,
you can’t, the calculations is done in the browser, not directly in SharePoint. If you need the information somewhere else, you’ll need to repeat the same calculation, e.g. if you’re sending the data in an email, you must recalculate it when building the email.
Hi Tom,
Thanks for this post and helping a eager learner.
I want to add an if statement to this calculation. I tried this JSON, but it only displays the JSON text and does not show a number. Any help would be appreciated.
{
“$schema”: “https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json”,
“elmType”: “div”,
“txtContent”: “=if([$Status]=’In Process’,floor((Number(@now)-Number([$Date_x0020_Held]))/(1000*60*60*24)),floor((Number([$Date_x0020_Resolved])-Number([$Date_x0020_Held]))/(1000*60*60*24))”
}
Hello Joe,
the ‘is equal’ condition is represented by 2x =, try to add the second one.
[$Status]=='In Process'That worked!
Can you please show how you would use div(sub(ticks(utcNow()),ticks([dateDynamicContent]),864000000000)
in an action in a Flow? I want to send a reminder based on the modified date of the list item. I have used JSON to show the days since item was last modified in my SP list. Every 3 months I want to send the assignee of the list item an email notification if the item has not been modified for x amount of days.
Thanks!
Hello Megan,
you just have to replace the [dateDynamicContent] part with the dynamic content that represents the Modified date, e.g.
div(sub(ticks(utcNow()),ticks(items('Apply_to_each')?['Modified'])),864000000000).hi tom, what formula should we use, and after that should we add this json formula to make this automated?
Hello ismail,
I’m sorry but I don’t understand the question, what are you trying to achieve? The whole json including the formula is in the code snippet.
Thank for very much for posting this. I followed your approach though and each row is returning the value 18850 in the column where I applied your code. Any thoughts on why that is, what I am doing wrong. Thank you. Mark
Never mind, I think i wasn’t inputting the reference field name correctly. Another question. Is it possible to then add more JSON to have a data bar associated with these value?
Hello Mark,
you can add a style to the JSON as shown in the 2nd comment, but since the value is calculated on the client side, you must repeat the whole calculation also in the style section, e.g.
"style": {
"color": "=if(floor((Number(@now)-Number([$Created]))/(1000*60*60*24)) > 10, '#ff0000', '')"
}
i have the same problem and i don’t know how to fix it
hi tom, I am working on this employee list and i have added this column that contains today’s date so it would be easier for me to calculate when an employee started working and when his contract ends, by using power automate the goal is to be sent an email when an employee’s contract is about to finish, problem is that it says flow ran successfully but expression in my condition is false and i don’t receive any emails, any suggestions?
Hello ismail,
the number of days is calculated only in the browser, it’s not stored in the item itself. To send a reminder you must do the whole calculation again in the flow, there’s already a post on how to do this.
Hi Tom,
that is a very helpful one. I just added it to one of my lists. One addition I would like (but failed to implement: Only show positive values.
I compare today to a future date and once that day has come, I Don´t want to have negative numbers in my list, just a blank. I tried some “if” magic, but all failed. What´s the smartest way to d this?
Hello Christoph,
you can add the “display” property into “style” in the same way as in this post: https://tomriha.com/hide-json-built-hyperlink-in-sharepoint-list-if-theres-no-underlying-link/
"style": {"display": "=if(floor((Number(@now)-Number([$Created]))/(1000*60*60*24)) < 0, 'none', 'inherit')" }
Great! Thanks, Tom!
I keep getting the same value 19072 for each row? I confirmed my date column ($Calculated) was keyed correctly.
{
“$schema”: “https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json”,
“elmType”: “div”,
“txtContent”: “=floor((Number(@now)-Number([$Calculated]))/(1000*60*60*24))”
Hello Mike,
isn’t the $Calculated a calculated field? I’m not sure if it’ll work as the whole solution is designed to replace calculated fields.
This JSON:
{ “$schema”: https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json, “elmType”: “div”, “txtContent”: “=floor((Number(@now)-Number([$Created]))/(1000*60*60*24))” }
But instead of Created date, in my SharePoint List, the column to compare to is: Calibration expiration date
I tried this:
{ “$schema”: https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json, “elmType”: “div”, “txtContent”: “=floor((Number(@now)-Number([$Calibration expiration date]))/(1000*60*60*24))” }
But it just displays the JSON. I’m sure it’s something simple I am missing… Would you mind letting me know? Thank you, btw, for this and all your other very helpful articles!
Hello Lauri,
you must use the column internal name – no spaces, no special characters (https://tomriha.com/what-is-sharepoint-column-internal-name-and-where-to-find-it/)
Hi Tom – do you know how to do this when ref to another column that has the date in? So, expected outcome would be – if date in col X is over 365 past, then red bg, else green bg (and text saying “blank date” if col X doesn’t have a date populated?)
Hello S,
all the columns are referenced by their internal name, e.g. [$CustomDateColumn], so just use this syntax to include other columns in the formula(s).
Hi Tom –
I think I am not using the correct syntax for the date field I want to use (Referral Date). When I test using [$Create], I get a valid number of days, but when I switch to use [$Referral Date], I get the JSON statement in my column. Here is my JSON. What am I missing?
{“$schema”:”https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json”,”elmType”:”div”,”txtContent”:”=floor((Number(@now)-Number([$Referral Date]))/(1000*60*60*24))”}
Thank you
Hello Tammy,
you must always use the column internal name – no spaces, no special characters, etc.: https://tomriha.com/what-is-sharepoint-column-internal-name-and-where-to-find-it/
Hi Tom, are you able to use the above formula to calculate business/working days? Not all days between ‘today’ and the created date?
And, how would I format the age to show as ‘sp-css-color-CoralFont’ with the background ‘sp-css-backgroundColor-BgCoral’
Like the sharepoint formatting default options allow you to show the field as a fully coloured in box, with the font
I tried to look at their JSON for it… but I got totally lost as to which part I should include and where…
Hello France,
that would be a hell of a JSON to calculate also with business/working days, I’m not sure anyone would be willing to spend the time to create it (if it’s even possible).
If you want to use classes in the JSON then it must be in the “attributes” element, as shown in the example here: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#conditional-formatting-based-on-a-number-range-basic
For example:
{
“$schema”: “https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json”,
“elmType”: “div”,
“txtContent”: “=floor((Number(@now)-Number([$Created]))/(1000*60*60*24))”,
“attributes”: {
“class”: “=if(floor((Number(@now)-Number([$Created]))/(1000*60*60*24)) > 1, ‘sp-css-backgroundColor-BgCoral sp-css-color-CoralFont’, ”)”
}
}
In Sharepoint, I want to have 2 columns, one with an expired date and the other a status column active or expired( default active). When a contract expires, let’s use today’s date, I want the status column to change to expired. Does anyone have a suggestion for a JSON formula? It seems pretty easy, but I am not sure what to use. Today I have to use a report to see which ones have expired and change the status manually and do not want to do that any longer.
Hello Heather,
JSON formatting is used only to display data in some format, it doesn’t update anything on the background. If you want to change status in another column then you’ll need a flow similar to a reminder: https://tomriha.com/how-to-build-basic-reminder-flow-using-power-automate-and-sharepoint/. ‘Get items’ where the date is equal to today and update their status.
Hi Tom, excelent post, thanks for sharing, I have a question, how could you express the result in days & hours format?
Hello Pedro,
you probably could do that, but but the JSON will be quite complicated, you’d have to calculate hours instead of days, divide it by 24 to get the number of days and then modulo by 24 to get the remaining number of hours.
Hi Tom,
This post is a life saver for me. Although, could you suggest how to make sure this date difference value is also populated in excel export? I am simply exporting the list to excel, but the value doesn’t appear. I am not using power automate.
Hello Mayur,
to get the value into Excel you’ll have to do the calculation somewhere before/during the export and store the values, this solution is to “view only”.
Thanks for the share, Tom.
I had JSON code showing up until I used the internal column name.
Now that I corrected that, I now have nothing showing in the column.
Any ideas?
The type of information in this column is:
Number (1, 1.0, 100)
Require that this column contains information:
No
Enforce unique values:
No
Number of decimal places:
Automatic
Default value:
Number
_____________________________________________
{
“$schema”: “https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json”,
“elmType”: “div”,
“txtContent”: “=floor((Number(@now)-Number([Event_x0020_Date]))/(1000*60*60*24))”
}
_____________________________________________
The $ was lacking.
Thanks again for sharing!
Hi All,
This is really useful information, however I need to highlight entire based nested conditions, like >30 and <180 – amber, <30 red and others green.
can anyone please help.
Regards,
Swati
Hello Swati,
like this: https://tomriha.com/how-to-highlight-whole-sharepoint-row-based-on-calculated-date/
Entire Row*
Good day how will i customize it where in i want it to be hours diff. instead of days?
Hello Stephen,
just divide it with a different number, I put a table here: https://tomriha.com/how-to-calculate-difference-between-two-times-in-power-automate/
Hi Tom.
Very useful post thank you.
Quick question: Is there a way to get the workday difference from Monday to Friday? Right now, it is counting all days, but I need to count only workdays.
Regards,