Lars Nielsen's Discoveries

August 15, 2010

Calculated field for age or time passed

Filed under: Customization,SharePoint — Lars Nielsen @ 10:09 pm
Tags:

A short post on how to create a calculated field that shows someone’s age, or how much time has passed since a date (for example, how long since a document was last modified).  I’m just pulling together various blog posts that cover this into a simple set of instructions. So let’s say you have a list of people with a column called DateOfBirth, and you want a calculated field that shows their age in years and months.   To work out the age:

Step 1 – Create a new column in the document library called Today (the name is important), a single text column

Step 2 – Create a calculated column called Age – it doesn’t matter what you name it, again single text. The formula for the Age column is:


=IF( [DateOfBirth]="" , "" , DATEDIF( [DateOfBirth], [Today], "Y") & "y " & DATEDIF( [DateOfBirth], [Today], "YM") & "m" ) )

Step 3 – Save the calculated column, and then delete the Today column. The Age column will show the age in the form “35y 4m”.  You can use the DATEDIF function to pull back the age in other ways, e.g. days, completed months.  If the Age column is showing #NUM! it’s probably because the DateOfBirth value is after today’s date, so DATEDIF is returning a negative number.

To edit the Age formula, you first need to create the dummy Today field again, and then delete it again after making changes to the formula. Obviously you can use the same technique to show how much time has elapsed since a document was last changed – just use [Modifed] instead of [DateOfBirth] in the formula.

Advertisements

2 Comments »

  1. It doesn’t work Lars – check back tomorrow and you will see your Age column has not been updated and is showing yesterdays data.

    http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/

    Comment by Ryan — August 16, 2010 @ 1:24 pm

  2. Ryan yes you’re dead right, thanks. This looks like a good technique: http://sharepointapplied.com/2009/04/23/useless-calculated-column-today-trick-xsl-today-to-the-rescue/

    Comment by Lars Nielsen — September 19, 2010 @ 12:20 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: