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

EDIT. I just saw this post Link which had some formulas such as which I am trying such as
=TEXT(NOW(),"mm/dd/yyyy") - Created
But I am trying them out



13 Replies

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.



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

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.

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


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": "",
"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



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.



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": "",
"elmType": "div",
"txtContent": "=substring(toString(Number(@now-@currentField)/86400000/365),0,indexOf(toString(Number(@now-@currentField)/86400000/365),'.'))"

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": "",
"elmType": "div",
"txtContent": "=substring(toString(Number(@now-@currentField)/86400000),0,indexOf(toString(Number(@now-@currentField)/86400000),'.'))"

best response confirmed by Norman Young (MVP)

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)

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).
Worked like a charm for me! Thanks

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.