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!