SharePoint Blogs / SharePoint University
SharePoint Blogs and SharePoint University - all in one place!
The Boiler Room - Mark Kruger (Moving to http:www.sharepointbloggers.com ) » HowTo: Using SharePoint Calculated Columns to Display a List Item as "X" Days Old
Need SharePoint Training? Attend a SharePoint Bootcamp!

Please delete cookies related to sharepointblogs.com and sharepointu.com to resolve login issues!

HowTo: Using SharePoint Calculated Columns to Display a List Item as "X" Days Old
The Boiler Room - Mark Kruger (Moving to http:www.sharepointbloggers.com )

Syndication

Recent Posts

News

  • My SharePoint Blog is moving: Mark Kruger's SharePoint Blog @ SharePointBloggers.com Interested in consulting work or becoming a consultant? Contact Me.

    - Quick Links -
    <script> google_ad_client = "pub-5278830398312697"; google_ad_width = 120; google_ad_height = 600; google_ad_format = "120x600_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = ["336699","DFF2FD","CCCCCC"]; google_color_bg = ["FFFFFF","DFF2FD","FFFFFF"]; google_color_link = ["0000FF","0000CC","000000"]; google_color_url = ["008000","008000","666666"]; google_color_text = ["000000","000000","333333"]; </script>
    <script> google_ad_client = "pub-5278830398312697"; google_ad_width = 120; google_ad_height = 90; google_ad_format = "120x90_0ads_al_s"; google_ad_channel =""; google_color_border = "336699"; google_color_bg = "FFFFFF"; google_color_link = "0000FF"; google_color_url = "008000"; google_color_text = "000000"; </script>
    <script> google_ad_client = "pub-5278830398312697"; google_ad_width = 110; google_ad_height = 32; google_ad_format = "110x32_as_rimg"; google_cpa_choice = "CAAQ0ZGazgEaCNfGe9Y_ARDqKPmNxXQ"; </script>
    - Blog Bits -
     My Professional Profile




    Subscribe in NewsGator Online
    Add to My MSN
    Add to Google

    Subscribe with Bloglines

    <script> var data, p; var agt=navigator.userAgent.toLowerCase(); p='http'; if((location.href.substr(0,6)=='https:')||(location.href.substr(0,6)=='HTTPS:')) {p='https';} data = '&r=' + escape(document.referrer) + '&n=' + escape(navigator.userAgent) + '&p=' + escape(navigator.userAgent) + '&g=' + escape(document.location.href); if(navigator.userAgent.substring(0,1)>'3') {data = data + '&sd=' + screen.colorDepth + '&sw=' + escape(screen.width+ 'x'+screen.height)};document.write(''); document.write(''); document.write(''); </script>
    <script> var AFS_Account="00721733"; var AFS_Tracker="0009"; var AFS_Server="www7"; var AFS_Page="SharePoint Blog"; var AFS_Url="http://www.sharepointblogs.com/mkruger"; </script> <script> </script>


    Tags:

    Translate Text or URLs
    <script> _uacct = "UA-67892-3"; urchinTracker(); </script> Who links to my website?

Archives

 

HowTo: Using SharePoint Calculated Columns to Display a List Item as "X" Days Old

 

I've had numerious requests asking how to display a List Item's age.  Well, in order to do this you'll have to first refer to my previous blog post:

Using [Today] in a Calculated Formula (Birthday Lists) which explained how to enable the [Today] functionality within a SharePoint lists' calculated column.  The example provided explained how you could create a Birthday List to display a list of contacts who have birthdays in the current month.

Ok, now on to this task.  First you'll need to create your Today column as mentioned in the previous post.  Then you'll then need to create a new calculated column in your SharePoint list (“Post is X Days Old“) and include the following formula for the calculated column:

=YEAR(Today)-YEAR(Created)-IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created), DAY(Today)<DAY(Created))),1,0)&" years, "&MONTH(Today)-MONTH(Created)+IF(AND(MONTH(Today) <=MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today) >=DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),-1)))&" months, "&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&" days"

Once that column is created, you can delete the “Today” column and view the list. The result of each list item will look something like this depending on the age of the item:

I hope this tip answers the questions I was receiving. :)

 

References: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q214094

 

I just had a comment below that I thought I would bring up to the article level. 

Question: Can you simplify this formula to just display days old?

Answer:  Yes, by simply using the following

= Today - Created 

Just make sure you format the calculated column to return a number and set the decimal to zero otherwise you'll get decimal places respresenting hours.

Note: Remember from the previous article that the formula will not calculate all list items on refresh but rather by the addition and deletion of a new “Today” column or by editing each of the list items individually.  However, you can code this to make it more dynamic but this is just a tip to get people in the right direction.  

 

Posted by Mark Kruger

Posted 06-26-2007 11:03 AM by mkruger
Need SharePoint Training? Attend a SharePoint Bootcamp!
Posts (c) their respective authors. Everything else (c) 2009 SharePoint Experts, Inc.