Forum Discussion
Create an 'Age/Days old' column in SharePoint List
- Feb 17, 2020
Hi Norman Young
The Number function of @now (& date fields) returns the value in milliseconds.
The value 86,400,000 represents 1 day in milliseconds
( 1000 x 60 x 60 x 24 )
Yes, by removing the /365 your result will be in days
For month result, you will have have to replace /365 with /30 (this will not be 100% accurate result due different number of days in a month)
Hi NabeelSalie,
I like the approach. I tested and it seems to be trimming the first number of the age. I used 12/31/1971 for the Age value and the result is 8. The JSON was not modified.
Again, a great approach.
Norm
Hi Norman Young
You are right, to have pull through the first letter, replace starting index value of "1" in the JSON I provided with a starting index of 0
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=substring(toString(Number(@now-@currentField)/86400000/365),0,indexOf(toString(Number(@now-@currentField)/86400000/365),'.'))"
}
- Norman YoungFeb 16, 2020MVP
Nice NabeelSalie.
If we remove the "/365" from the formula it gives the age in days? Or if we replace "/365" with "/12" we age in months and so on?
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=substring(toString(Number(@now-@currentField)/86400000),0,indexOf(toString(Number(@now-@currentField)/86400000),'.'))"
}- NabeelSalieFeb 17, 2020Copper Contributor
Hi Norman Young
The Number function of @now (& date fields) returns the value in milliseconds.
The value 86,400,000 represents 1 day in milliseconds
( 1000 x 60 x 60 x 24 )
Yes, by removing the /365 your result will be in days
For month result, you will have have to replace /365 with /30 (this will not be 100% accurate result due different number of days in a month)