*Figure 1. Excel Data Validation Using the WEEKDAY Function.*

We can utilize a custom data validation formula derived from the Excel WEEKDAY function, if we want to permit a user to enter only dates that occur during the week (Monday – Friday).

**Formula Syntax**

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

This formula will return any Saturday and Sunday (weekend) entries as FALSE.

**How to use the Data Validation with the WEEKDAY Function in Excel**

The WEEKDAY function in our custom validation formula gets a number value, (1-7), which corresponds to a week starting Monday (1) and finishing Sunday (7).

The return type argument for WEEKDAY is given as 2 to obtain a number for a Monday-based week.

The result of the WEEKDAY operation is matched against the number value 6 and if the date turns out to be a Saturday or Sunday, validation is FALSE.

We can achieve all this with 3 simple steps;

- Properly arrange the data available for validation within columns of our Excel sheet.

Be sure to provide empty cells for Excel to return the desired results.

* Figure 2. Excel Data Validation Using the WEEKDAY Function.*

Our purpose here is to ensure that only dates occurring between Monday and Friday (weekdays) are entered into cells of our worksheet example.

- The custom validation formula for cell C2 above is;

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

* Figure 3. Excel Data Validation Using the WEEKDAY Function.*

Excel returned the result as TRUE since the date in cell B2 falls on a weekday.

- Copy and paste the modified version of the custom validation formula in the other cells in the RESULTS column to get the other results.

* Figure 4. Excel Data Validation Using the WEEKDAY Function.*

Whenever a user adds or changes a cell value, data validation rules are triggered.

* Figure 5. Final Result.*

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

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes.

## Leave a Comment