How to give Validate a Due Date in a SharePoint 2010 List

One of the great new features that end-users love in SharePoint 2010 is the ability to validate input. It provides an easy way to make sure that users enter the correct type of data in a metadata field. However, there are a couple questions users often have. The first one is why are there two different places to do it (Column Validation on the Column Settings page and Validation Settings on the List Settings page)? The second question users often have is how can I reference other columns in my validation rules? In this blog post, we’ll answer these two questions from a common case study.

The Problem – How To Disallow a Due Date that is in the Past?
One of the common things people want to do is make sure that people do not set due dates for tasks (or other types of lists) in the past.

Usually the first thing a you will try is to set the validation to [Due Date] > [Today]. This makes sense because you are already use to using [Today] to filter views. However, when you try to do this.

You receive an error telling you, “The formula cannot refer to another column. Check the formula for spelling mistakes or change the column to this column.

So, the next thing you try is to set the field to validate based on the Creation date. So, you change your validation formula to [Due Date] > [Created] and try again:

Unfortunately, this yields the same error message about The formula cannot refer to another column.

A third option you may consider is to use the Today function to create a formula like: [Due Date] > Today().

SharePoint seems to like this formula and correctly displays the error message:

However, there  is a problem with this solution as well. The validation will occur each time someone edits the task. So, if someone edits the task three days from now, this validation will fail unless the Due Date changes to a date later than the current day. Aaarrggghhh!!!!!

The Solution – So, What’s a SharePoint User to Do?

You’ve probably noticed that there are two places you can set column validation. The one you use most of the time is on the Column Settings page. However there is also one on the list settings page. It’s there at the top. You’ve seen it a thousand times and just never noticed it before. Click on that one.

In the Validation Settings link on the List Settings page, you can validate one column against another. Here you can see that I’ve set the[Due Date] > [Created].

If your users attempt to enter a due date that is before the creation date, they will see the message you entered.

So, you’ve learned a couple things. First, there is a distinct difference between the Validate Settings link on the column page and the Validate Settings link on the Column Settings page: the one on the List Settings page allows you to compare columns, while the one on the column settings page doesn’t. Second, the validation error messages from these two appear in different places on the form: validation error messages created on the column settings page will appear in the column section on the form, while validation error messages created on the List Settings page will appear at the top of the form.