Forum Discussion

testio2's avatar
testio2
Brass Contributor
Apr 17, 2019

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

 

  • NabeelSalie's avatar
    NabeelSalie
    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)

  • DEC_Studio's avatar
    DEC_Studio
    Copper Contributor

    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.

  • MDiehl's avatar
    MDiehl
    Copper Contributor
    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).
    • ivorycassiopeia's avatar
      ivorycassiopeia
      Copper Contributor
      O.M.G. - thank you so much for this!!! I have been searching all over for this and finally found your post!!!
      • stevenfilanowicz's avatar
        stevenfilanowicz
        Copper Contributor

        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!

  • NabeelSalie's avatar
    NabeelSalie
    Copper Contributor

    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

    • Norman Young's avatar
      Norman Young
      MVP

      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 

  • 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-formatting-based-on-date-ranges

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

    • LukeC's avatar
      LukeC
      Brass Contributor

      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

Resources