Monday, December 28, 2009

Selecting Dates from a SharePoint Dropdown List

Assume the following Scenario:
You need a week list with the week's Monday date and you want it to be a validation (dropdown) list for a column in another list. SharePoint 2007 doesn't support dates in a dropdown selection list so dates won't appear as an option for validation (dropdown).

The work around is to create the column as a single line text. In order to show the 'dates' in the right order you need to keep an exact format like 2009/10/22. You can use Excel to easily prepare such a list and copy the data into the SharePoint list opened in a datasheet mode. However, Excel treat date cells like dates so extra steps are needed:
  • Type the first Monday's date of your choice. (Let's say in A1)
  • Enter in A2: =A1+7 to calculate the next Monday.
  • Drage down cell A2 as many cells as needed to get the full week list.
  • In B1 enter: =TEXT(A1,"YYYY/MM/DD"). This will transform the date into a string with the exact format we need.
  • Drag B1 down to the last filled in row. 
  • Copy the cells in B and use Paste Spacial to paste the values. This will get rid of the formulas and leave the values in column B.
  • Copy the range and paste it into the list in SharePint (opened in a datasheet mode)
Now you can lookup at this column as a dropdown (validation) in another list.

If you need, for example, to filter the list be the year, you can create a calculated column with the formula
=LEFT([The Week of:],4)  (assuming that the column mane is 'The Week of:" and it contains 4 characters).

No comments:

Post a Comment

Enter your comment here