Expiry columns and the [Today] value in SharePoint 2003

I’ve been trying to create some complex views on a custom list in SharePoint 2003. One of the problems is that some items in the list have expiry dates, and some do not. I need to filter the views based on a “category” column, showing only those items which have not expired. Problem is, this includes items whose expiry dates have passed, and also items which don’t have an expiry date at all. Eventually, I’ve found a workaround.

What I really need is a filter something like this:

[Category] is equal to “Category Name 1″ AND ([Expires] is equal to “” OR [Expires] is greater than or equal to [Today])

Unfortunately, there’s no way to group the two statements either side of the OR within SharePoint’s list view Filter options. I’m not even sure how SharePoint treats the three statements when you mix AND and OR operators.

My next approach was to try and create a calculated column (of type “Yes/No”), called HasNotExpired. This would contain the following formula:

=OR([Expires] = “”, [Expires] >= [Today])

Trouble is, if you try and put [Today] in a calculated column, SharePoint complains that the column contains “volatile data”, and the formula can’t be used.

An alleged workaround for this limitation sounds as though it doesn’t work at all – i.e. the value of [Today] is only updated when you edit the column. So that’s no use either.

In the end, I have found an alternative workaround. I added a calculated column, called CalculatedExpiry, which returns a data type of “Date and Time”. This calculated column uses the following formula:

=IF([Expires]=”", DATE(2099, 1, 1), [Expires])

This column will contain a false expiry date of 1 Jan 2099 for items with no expiry date. Items with a real expiry date will use their actual expiry date.

This then enables me to use the following filter on my list:

[Category] is equal to “Category Name 1″ AND [CalculatedExpires] is greater than or equal to [Today]

…and everything is happy!

6 thoughts on “Expiry columns and the [Today] value in SharePoint 2003

  1. That’s just what I needed! Thank you.

    I only use Sharepoint as a minor part of my job, so silly problems like this are difficult to rectify.

    This seems to be the most elegant solution for this particular problem though.

  2. Pingback: bloggingIT » Blog Archive » Don’t hide my announcements!

  3. I like to add that I had a problem with this when working with SharePoint variations!

    In the English variation the formula looked like this:
    =IF(Expires=”",DATE(2099,1,1),Expires)

    but in the Danish variation it had to look like this:
    =IF(Expires=”";DATE(2099;1;1);Expires)

    So just changing from variation (language) to another caused the problem.

    Just wanted to let you know.

  4. THANK YOU SO MUCH FOR THIS FORMULA. I’m a power user of SharePoint and sometimes adding a formula to columns are pretty tricky.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>