Create an 'Age/Days old' column in SharePoint List

[testio2]

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

EDIT. I just saw this post Link which had some formulas such as which I am trying such as

==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

[#2]

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

[#3]

@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:

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#apply-fo...

https://techcommunity.microsoft.com/t5/SharePoint/Calculate-a-duration-in-column-formatting/td-p/293...

[#4]

@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.

[#5]

What Christophe means is to use the @now in either column or view formatting, as per the linked references.

As an example, you could apply formatting to the 'created' column, so that rather than displaying the actual value, it displays the age of the item. That display would be updated at the point of loading the page / view - so would not rely on someone editing the items.

[#6]

@Rob Ellis Understood. Thanks for that clarification. I need the underlying data to be updated as well, basically for a Days Open columns, so that solution won't work. I'm resorting to Flow updating items daily instead.

[#7]

@testio2 

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

[#8]

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 

[#9]

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),'.'))"
}

[#10]

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),'.'))"
}

[#11 · Accepted solution]

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)

[#12]

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).

[#13]

Worked like a charm for me! Thanks

[#14]

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.

[#15]

@LukeC how were u able to fix the issue for it ageing ?

[#16]

O.M.G. - thank you so much for this!!! I have been searching all over for this and finally found your post!!!

[#17]

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

[#18]

@DEC_Studio 

Please assist on this. Some have error in value. Attached