SOLVED

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

Highlighted
Contributor

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

 

image.png

10 Replies
Highlighted

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

Highlighted

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

Highlighted

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

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

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

Highlighted

@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

Highlighted

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 

Highlighted

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

Highlighted

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

Highlighted
Best Response confirmed by Norman Young (MVP)
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)