- June
- 26
- 2007
HowTo: Using SharePoint Calculated Columns to Display a List Item as "X" Days Old
Posted by mkruger
1 Comment »
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
Here is a way to create /delete column “Today” automatically:
First of all, sorry about my poor english.
I spent days looking for a solution for “Today” issue. I don’t know anything about programming, I am system admin so It was kind of hard.
So this is my workaround to solve it using the dumb column “today” and the fantastic extra functions for stsadm.
1.- Download wsp for custom properties of stsadm
http://stsadm.blogspot.com/
2.- In any list create field “Today” manually
3.- Export field “Today” into a xml file:
stsadm –o gl-exportlistfield -url “”http://server/site1/SourceList/AllItems.aspx” -fielddisplayname “Today” -outputfile “c:\Today.xml”
4.- Import and delete column “Today” into your site. It will update your values
stsadm -o gl-importlistfield -url “http://server/site1/List1/AllItems.aspx” -inputfile “c:\TodayCol.xml”
stsadm -o gl-deletelistfield -url “http://server/site1/List1/AllItems.aspx” -fielddisplayname “Today”
5.- Create BAT file with the point 4 and schedule it to run once on weekdays.
PS: In case you don’t know how work with BAT files:
run CMD
then run this commands:
___________________________________________________
cd\
cd:
copy con refreshToday.bat
Echo. Refresh column today
stsadm -o gl-importlistfield -url “http://server/site1/List1/AllItems.aspx” -inputfile “c:\TodayCol.xml”
stsadm -o gl-deletelistfield -url “http://server/site1/List1/AllItems.aspx” -fielddisplayname “Today”
(Ctrl +Z)
schtasks /create /tn “Refresh Today” /tr c:\refreshToday.bat /sc daily /mo 24 /st 01:00:00 /sd 12/15/2009
exit
_________________________________________________