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