We can use the **Excel WEEKDAY function** to ensure that **data validation** allows weekday only. The steps below will walk through the process of using **Data Validation to allow weekday only.**

*Figure 1 – Example of Data Validation to allow weekday only*

**General Formula**

**=WEEKDAY(A1,2)<6**

**Formula**

**=WEEKDAY(C5,2)<6**

**Setting up the Data**

- We will set up our data in
**Columns B and C** **Column B**will contain our**Groceries****Column C**will contain our dates of purchase

* Figure 2 – Setting up the Data*

**Applying the WEEKDAY customized function**

- We will highlight
**Cells C5**to**C7** - We will click on
**Data Validation**in the**Data tab**within the ribbon at the top of the worksheet

* Figure 3 – Clicking on Data tab*

- We will check the
**ignore blank**box, select**custom,**and enter the formula below in the**formula box**

**=WEEKDAY(C5,2)<6** - We will press
**OK** - This will allow weekdays only

* Figure 4 – Applying the WEEKDAY customized function*

**An Alternative Formula for Data validation**

We can also create a return value argument that would allow the dates from 1 (Sunday) to 7 (Saturday).

- We will again highlight on Cells
**C5 to C7**and select**Data Validation** - We will select
**custom**, and enter this formula below in the**formula dialog box**

**=AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7)** - We will press
**OK**. This would return TRUE for all values if they fall within Saturday to Sunday.

* Figure 5 – Alternative formula for Data Validation*

**Explanation**

We use the Data validation rules to restrict our cells so any user can only add values within the specific restriction. For our formula, the return value, 2 is a Monday-based number. This would be compared by Excel against 6. Therefore, any values less than 6 will return true. If the value is more than 6, the date is Sunday or Saturday, then validation fails.

**Note**

- If we wish to allow only dates that occur in the weekend such as Sunday or Saturday, then we can use this formula:

**=WEEKDAY(C5,2)>5** - The Cell reference in the data validation formulas are always relative to the upper left cell, hence the reason our formula contains, C5.

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment