Parsing Dates Into Archive Buckets in a Calculated Column in SharePoint

I needed to put sets of news articles into buckets in a list so that I could allow users to filter based on the time period in which the articles were published.  To do this, I created a calculated column as a Site Column (for reusability).

Column name: Archive Period

Calculation: =IF([Publish Date]<([Today]-365),”Year” & TEXT(YEAR([Publish Date]),”####”),IF([Publish Date]>0,TEXT(29*MONTH([Publish Date]),”mmmm”)&” “&YEAR([Publish Date]),”Publish Date not set.”))

This formula returns a value like “September 2007” (when the Publish Date is within the last 365 days) or “Year 2006” (when the Publish Date is more than 365 days ago) which is used to drive the population of the archive views.  (We decided to set up a Publish Date column that could be managed separately from the Created Date or Modified Date.)

SharePoint will not allow you to use the Today column in your calculation.  The way to get this to work is to add your own column to the list you are working with called Today.  The type of column for your Today column is basically irrelevant.  This tricks SharePoint because it “thinks” that you are using your own column.  Once you have your calculated column’s formula set up correctly, delete your column called Today and all will work as you wanted.  Keep in mind that any time you want to edit the calculated column, you will need to add back your “fake” Today column.

Note also that Excel is a great place to build up these formulas so that you can take advantage of the contextual help, etc.  See this post.