Forum Discussion
Solved
Create an 'Age/Days old' column in SharePoint List
Hello. I am using SharePoint Online with a sharepoint list
I wanted to create a column which is called 'Age' This would show many days old an item is
What I currently have (Please see pic) is:
1) Created a calculated column called 'Age'
2) Added the formula =TODAY() - Created. (I had tried just TODAY-Created but it gave same reply /saw the () online)
3) Put the data type to return as a number
This seemed to look ok as it said e.g. 5 days old. But you have to click on the item and edit it and only then will the age column update...It does not increase every day so 5,6, 7, etc on its own.
I have to go to the item, make some form of edit and only then will it update from say 5 days old to 7 days
Is there anyway to have this automatic column that just shows a number of how old the item is?
Thank you!!!
==DATEVALUE(TEXT(NOW(),"mm/dd/yyyy"))-DATEVALUE(TEXT(Created,"mm/dd/yyyy"))
and
=TEXT(NOW(),"mm/dd/yyyy") - Created
But I am trying them out
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)
Hello Everyone is the issue already resolved? if not kindly reply my comment i wish to assist you all on this specific issue: you can email me in my outlook account: email address removed for privacy reasons
Please check below if this is the result you all need?
This is the formula i Used:
My source for the Service Years is my Date Installed Column.
Here's the formula:
=IF([Date Installed]="","",INT((TODAY()-[Date Installed])/365.25)&" Year(s)"&INT(MOD((TODAY()-[Date Installed])/365.25,1)*12)&"month(s) and "&INT(MOD((TODAY()-[Date Installed])/30.4375,1)*30.4375)&"day(s)")
Note: Please change the Date Installed for the column name that you want to formulate.
- In a calculated column, use:
=IF(ISBLANK([Date reported]), 0,(DATEDIF([Date reported],TODAY(),"d")))
If anyone jumps on here and is displaying both the date and a count for how many days have gone by (in my case, how old a post is: 2 days, 17 days, etc.). You'll want to identify your own Column and this takes into account the case that the identified date column has not been filled in (will display 0).- O.M.G. - thank you so much for this!!! I have been searching all over for this and finally found your post!!!
DEC_Studio This is great! two questions, will this auto update each year? Second question, do you happen to know how to add an extra format to this? I would love to be able to have it highlight Red once the Age column reached a certain point.
What I tried to do, was alter your formula to get just Days old, and removed years and months, but that doesn't work if I wind up going past 30 days old. My plan with that was to find some Formatting forumla to make it go red if the item was 90 days aged.Going back to your formula where I have years, months, Days is great, but ideally if there was a way to still have it format red once it hits 3 months?
Thanks so much!
- Worked like a charm for me! Thanks
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
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),'.'))"
}
testio2 you cannot do this using a calculated column, as it stores static information.
If you are using the modern experience in SharePoint Online, check out column formatting and view formatting. You'll need to use @now, which resolves to the current date/time.
Here are two references to get you started:
Christophe Humbert actually NOW() doesn't resolve the issue of auto-updating calculated columns. It doesn't matter if you write your calculated columns with NOW() or TODAY(), the problem exists that the data becomes incorrect after one day unless you edit the item.
Hello testio2,
Is there anyway to have this automatic column that just shows a number of how old the item is?
Not using out of the box functionality. Consider using a Flow to update the Age column on a daily basis.
I hope this helps.
Norm
