Expiry columns and the [Today] value in SharePoint 2003

Posted by Dave on October 22nd, 2007

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!



Write a Comment

Take a moment to comment and tell us what you think. Some basic HTML is allowed for formatting.

Reader Comments

Comment:
1
Written by:
John
Posted on:
November 5th, 2007 at 1:03 pm

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.

Comment:
2
Written by:
bloggingIT » Blog Archive » Don’t hide my announcements!
Posted on:
November 5th, 2007 at 1:12 pm

[...] A quick Google later, and I found This Much I Know, which pointed me in the direction of creating a calculated column and then putting some code to place a new date in, if the expiry is blank. [...]

Comment:
3
Written by:
Aditi
Posted on:
February 13th, 2009 at 1:27 pm

Nice post.. It helped. Thanks..

Comment:
4
Written by:
Jesper Simonsen
Posted on:
January 5th, 2010 at 4:41 pm

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.