Posted by Johan Vermeire on 14/02/2012.

A lot of people already know the ‘Data Validation’ functionality in Excel. It restricts the input to a specific condition. Example: the user is only allowed to enter a value between 10 and 20; the user is only allowed to enter a date in the past… 

But what if you want to restrict the input to a condition that is not in the list? What if you want to restrict the input to dates that are in the weekend.

When you open the data validation window, you can select the option ‘Date’, but you can only enter criteria like ‘Between’,‘Less than’, ‘Equal to’, ….

 Follow these steps and you will have the validation: 

• Select the range of cells that you want to give the validation. I have selected the range B2:B10 and B2 is my active cell.
  
  
• Select ‘Data’ – ‘Data Validation’
• Modify the settings as in the following screenshot:
  
  We use the function ‘WEEKDAY’ to calculate the day of the week.  The second argument of the weekday function (the number 2) indicates that the first day of the week is Monday.  Thus, the result of the weekday function must be a number, greater than 5.
• Result: