Forum Discussion
SharePoint Online Date Calculation
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
- CindyZJun 27, 2018Iron Contributor
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.
- Pavel SheludkovJun 27, 2018Brass ContributorGood 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/ - Misty MillerOct 26, 2018Copper Contributor
Any insight on this?
- AbbyamanteSep 30, 2020Brass Contributor
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?
- asifkhawaja7Jan 20, 2021Copper Contributor
Abbyamante , just use the floor() function to round the number down.
"=floor((Number([$DueDate])-Number(@now))/86400000)"