How to 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:
image

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.”
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:

image

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().

image

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

image

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.

image

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].

image

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

image

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.

Happy Validating!

4 Comments

  1. SharePoint Help — Blog — How to Validate a Non-Required Column - March 27, 2012, 1:27 pm

    [...]  How to Validate a Due Date in a SharePoint 2010 List [...]

  2. Avatar of Elliot Dodson

    Elliot Dodson - May 21, 2012, 5:07 am

    Hi Ricky,

    I had the exact problem you described above and your sollution has solved it perfectly. One issue I have found is that he validation only takes affect when creating a new item in the list and not on item edit, therefore you can go and change the due date for any entries to be invalid once they have been created. Is this an issue that you have experienced? Do you have a fix?

    Thanks,

    Elliot

  3. Avatar of Ricky Spears

    Ricky Spears - May 21, 2012, 10:25 am

    Elliot – I’m not in a position where I can test it, but did you try using [Modified] instead of [Created]?

  4. Avatar of Elliot Dodson

    Elliot Dodson - May 21, 2012, 10:34 am

    Have now – Issue solved, Thanks a lot!

Leave a Reply