Go Back

Excel Data Validation Using the WEEKDAY Function

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;

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

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

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

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I was working with pivot tables and have a good understanding of how to build. One problem Ive run into and cant seem to solve is the dates in my filters. One of my filters, The Revised Promise Date and Actual Shipped Dates shows the detail date, IE 3/10/17, and allows me to filter. The dates I need like this are using the Original Promise and Need Dates as filters, those only allow me to filter by Month.
Solved by C. J. in 16 mins
I'd like this formula for a gantt chart to only display weekdays... =Project_Start-WEEKDAY(Project_Start,1)+2+7*(Display_Week-1)
Solved by O. S. in 26 mins
Hello I am trying to count the number of unique mondays in a data set. I am able to count the number of unique dates or count the number of mondays, but not able to combine the criteria. The only work around I found was to compare dates (if they are in sequential order) and run a WEEKDAY() formula, but I would prefer one step to do the calculation.
Solved by D. A. in 20 mins

Leave a Comment

avatar