SharePoint Online Date Calculation

[Cindy Zalme]

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. 

[#2]

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.

[#3]

What are you trying to accomplish with the calculation? That I would like more to know 🙂
Kind regards, John

[#4]

"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!  

[#5]

Thanks, Matt...may not be beautiful, but that will work!  

[#6]

If you are using the New experience interface than you can achieve it with JSON column formatting.

Here is the example: 

{
"$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
"elmType": "div",
"txtContent": {
"operator": "/",
"operands": [
{
"operator": "-",
"operands": [
{
"operator": "Number()",
"operands": [
"@now"
]
},
{
"operator": "Number()",
"operands": [
"[$DueDate]"
]
}
]
},
86400000
]
}
}

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.

Cheers

[#7]

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.  

[#8]

Good to hear.

I was first trying to use the calculated column but then realised that JSON formatting is much easier approach.

Also, if you haven't installed this amasing tool for working with JSON formating yet, do it.
https://thechriskent.com/2017/12/21/column-formatting-client-side-web-part-column-formatter/

[#9]

Any insight on this?

[#10]

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:

"=(Number([$DueDate])-Number(@now))/86400000"

[#11]

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!  

[#12]

Wow, thank you for sharing @Christophe Humbert. Now it looks really great, short and easy to read. 

[#13]

@Christophe Humbert is there any was to get the correct integer value of that 15 decimal number that is returned? 

---

@Christophe Humbert wrote:

In this case you would just write:

"=(Number([$DueDate])-Number(@now))/86400000"

---

[#14]

@Visuary Did you ever figure out how to display the value as an interger?

[#15]

@Pavel Sheludkov thank you for sharing this format. this works on my sharepoint list column. however, do you know how to remove the decimal points and just make it a round number of days?

[#16]

@Abbyamante , just use the floor() function to round the number down. 

"=floor((Number([$DueDate])-Number(@now))/86400000)"

[#17]

@CindyZ 

Change the column setting as single line of text instead of Number