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)
If anyone still interested in getting the Age (in years) to display, here is the formatting that I've used
The Age column is a DateField in SharePoint and I specify the birthday in that field.
The column formatting then uses the following:
{
"$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),'.'))"
}
The value returned is a number with long decimal e.g: 41.17125147541258
I then use the substring and indexOf method to display a trimmed value e.g: 41
Hope it helps
Update
Have corrected the incorrect substring parameter used of 1
The correct parameter to use is: 0
Thanks to Norman Young for highlighting the error
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
- NabeelSalieFeb 16, 2020Copper Contributor
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)