Oct 18 2017 05:34 PM
Oct 18 2017 05:34 PM
I'd like to calculate the number of days between [today] and the due date in a SharePoint Online task list. Not sure why this seems so difficult or why I can't readily find an answer online. Any ideas? Thanks so much ahead of time for your help.
Oct 19 2017 05:59 AM - edited Oct 19 2017 06:00 AM
The problem is SharePoint Online treats that as a calculation, and calculations are only refreshed when the item is modified in any way (at least, out of the box). The only way I could find to force the date to recalculate was to create a loop in SharePoint Designer or Flow to overwrite something in an item with its current value until due date = today, which technically changes nothing about the item while still counting as a modification, resulting in a refresh of a date difference calculation. It's an expensive workaround, especially on large lists with lots of due date items, but it's the only non-custom way I know of to manage this.
Oct 19 2017 10:30 PM
Oct 20 2017 05:20 AM - edited Oct 20 2017 05:39 AM
"How many days left until this item is due?" For now, I'm looking for all days since elimininating weekends may be a challenge for now. I have a start date and a due date. How many days between those two? Thanks!
Jun 27 2018 04:15 PM - edited Jun 27 2018 04:20 PM
If you are using the New experience interface than you can achieve it with JSON column formatting.
Here is the example:
You can see in the example that it converts "@now" and "[$DueDate]" into numbers, then subtract them. Finally, the subtraction result is divided by 86400000 - the number of milliseconds in a day.
Jun 27 2018 04:40 PM
Thanks very much for this and especially your explanation. I did some quick searching but was not able to quickly ascertain why it is returning NAN even though my column is formatted as a number.
Jun 27 2018 04:45 PM
Nov 30 2018 02:28 PM
Although @Pavel Sheludkov already provided an answer, I just wanted to mention that view and column formatting now accept formulas that significantly shorten the expression.
In this case you would just write:
Nov 30 2018 07:22 PM
Oh my gosh, Christophe! Thanks so much for your help and answer! Great to hear from you again. You helped me on some projects over three years ago!