Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc