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.


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.


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.


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!


  • Everyone who shows this trick forgets to mention that it really does not work. If you can back to your list next year, the calculation would be incorrect; [today] would reflect the current date of when the item was updated.

  •'re almost as old as Spiderman... *evil grin*

  • Could the year change trigger a workflow? So as things get older emails could be sent.

  • Although the workaround does work 'Today' when the calculation is saved and the Placeholder field is deleted, it will not work tomorrow, or the days after. Can't believe such a simple problem can not be solved with sharepoint out of the box. WOW.

  • Great write-up!
    I am still testing, but for those who are trying to get a dynamic today date, this website may help.

  • Thanks All,

    i hope some solve this issue to share it with us. i realy want number if date be dynamic as per today,

    Please if anyown can help me and support us.

    thanks a gain.

  • This doesn't work with SharePoint 2010. Not in Designer OR SharePoint site. Anyone disagree with this?

  • This isn't working..."today" doesn't update.

  • can't get this to work, i'm seeing 40,000 days as the age

  • calculated formula is incorrect
    it works fr me

  • Just to help clarify for other folk reading this stuff ... the lack or presence of a useable "Today" function in SharePoint depends on the >version< of SharePoint you are using. S/P 2010 allows the use of "Today()" in calculated fields, older version of S/P (such as S/P 2007) do NOT allow the "Today()" function.

    Here's the main pain point regardless of whether you can use the native "Today()" function or any of the various work-arounds: the results of your calculation are NOT dynamicly/automatically updated -- the calculated value will ONLY update when you edit and save your list. That means if you are calculating (for example) an elapsed time from a given date, you can't just view your list and see the correct value, you have to edit and save the list to force the calculated value to update.

Comments have been disabled for this content.