Aging Calculated Fields in SharePoint

A common question I get and SharePoint challenge is to show the age of something in SharePoint. A typical example is to show the age of a document in days, months, years, etc. Or you might want to keep track of some item (say a coin in a coin collection, or a server in your network). Here we’ll display the age of comic books in a collection in years from the current date. BTW, this isn’t really new as we’ve been doing these tricks for a few years now. I just thought I would share my take on it with the rest of the class.

Setup

First we need a list to keep track of our comic books. Create a new custom list and create a field called “Year Issued”. This should be a numeric field and will be the year the comic book was issued. Add a few entries to the list so you have something like this:

Notice that the Year Issued shows 1,962 rather than 1962. This is the default behaviour of a number field so you can either a) use a DataView web part and format this later or b) use a text field instead. We only have this problem because we’re trying to capture the year. While there is the Date field, it requires a date to be picked and it’s not quick to enter a year using the calendar control (especially if it’s old) so a text field might be more appropriate for this case, YMMV.

Magic

Now comes the magic. Create a new field called “Today”. Don’t worry about the type, just use the default but make sure you name it “Today” (without the quotes). Then create a new field called “Age” and make it a calculated field. Down in the calculation column, here’s the formula you use:

Notice the use of [Today] in the calculation formula. This is referencing our “placeholder” column we created earlier. Save the calculated column and back at the column list, find the Today column and click on it to edit it. Now in the edit screen, delete it. Yup, get rid of it. We don’t need it anymore.

Now take a look at the list, complete with our calculated field showing us the age of our comic books. Cool huh?

The Secret

Don’t believe the magicians trick that they won’t tell you how they did it. I’m going to. Built into SharePoint are some pre-determined names. [Today] happens to be one of them. For example, if you want a field to have a value of the current date when something is added to a list, just enter [Today] in the default value. This is great, however if you try to use [Today] in a calculated field you’ll get this message:

The secret is to create a placeholder field with same name of [Today]. When the column is validated and saved, it works because it thinks it’s using the column reference you added called [Today] (which you are, at creation of the calculated field). When you removed the column called [Today] the calculated field just used the internal [Today] reference which is that volatile function it couldn’t use before.

You might have noticed after you had the Age column and the placeholder Today column your list might have looked like this:

The calculation isn’t quite right here. That’s because the calculated column was using the existing Today field (a blank field) so the calculation was YEAR([Today])-[Year Issued] which translated to YEAR(0)-1962 and produced –63. YEAR(0) (or YEAR(“”) will result in 1899 as it’s value. Once you removed the blank Today column the calculation used the built-in [Today] value which is the current date and voila!

I know. Silly huh? One note about this “workaround”. If you ever need to edit that calculated field again, you’ll need to create the placeholder [Today] column again, otherwise you won’t be able to save it. Again, just create it, edit your calculated field, then delete it. No harm, no foul.

Variations

There are a lot of variations you can do with this, now that you know the trick. For example, rather than getting the information from the user in a text or numeric field, you can use a date field and then show the age in years, months, and days. Or you can use the built-in creation date compared against the Today trick to show you the age of a document in a document library.

Like I said, this isn’t new and ground shattering. Mark Kruger documented something like this here with a more complicated formula (for displaying the text “Post is X days old”). Chris Johnson has it documented here and Dessie Lunsform has a good post on it here along with links to some calculation formula references.

So be creative, experiment, and have fun!