Data Validation Allow Weekday Only? How to Set It

Yes, Excel’s data validation can allow only weekdays in a cell. You set it with a custom formula that checks the entered date’s day of the week; if it falls on Saturday or Sunday, the entry is rejected. This guide explains the exact formula, how to apply it, common pitfalls, and how to handle blank cells and existing data.

Formula

The core of weekday-only validation is the WEEKDAY function combined with a custom rule. The formula =WEEKDAY(A1,2)<=5 returns TRUE for Monday through Friday and FALSE for Saturday and Sunday. The second argument, 2, tells Excel to number days Monday=1 through Sunday=7. If you omit it, Excel uses Sunday=1 by default, so the formula would need to be =WEEKDAY(A1,2)<=5 to work correctly.

In the calculator above, the expression (WEEKDAY(d,2)<=5)*1 converts the TRUE/FALSE result into a 1 or 0, making it easy to test. The TEXT function shows the actual day name for verification.

How to Apply the Validation

Applying the rule takes only a few steps, but the exact menu names matter. Here’s the process for Excel 2016 and later (including Microsoft 365):

1. Select the cell or range where you want to restrict entries to weekdays.
2. Go to the Data tab and click Data Validation (in older versions it’s under the Data menu, then Validation).
3. In the Settings tab, choose Custom from the Allow drop-down list.
4. In the Formula box, enter =WEEKDAY(A1,2)<=5 — but replace A1 with the top-left cell of your selected range. If you start at B2, use =WEEKDAY(B2,2)<=5.
5. Click OK. Now any date that falls on a weekend will be rejected with an error alert.

You can also add an Input Message (a tooltip that appears when the cell is selected) and an Error Alert with a custom title and message like “Weekdays only� and “Please enter a date from Monday to Friday.�

Why the Formula Works

The WEEKDAY function returns a number from 1 to 7. With the second argument set to 2, Monday is 1, Tuesday is 2, and so on through Sunday which is 7. The comparison <=5 is TRUE only for numbers 1 through 5, which correspond to Monday through Friday. That’s exactly what you want.

If you leave out the ,2, Excel assumes Sunday=1, so Saturday becomes 7 and Sunday becomes 1. In that case, the formula =WEEKDAY(A1)<=5 would allow Sunday (1) but reject Monday (2) through Friday (6) — the exact opposite of what you want. This is the most common mistake when setting up this validation.

Common Problems and Fixes

Several issues can make the validation appear broken. Here are the most frequent ones and how to solve them:

• Blank cells are rejected. If you want to allow blanks, modify the formula to =OR(A1="",WEEKDAY(A1,2)<=5). The OR function returns TRUE if the cell is empty or if it’s a weekday.
• Validation only applies to new entries. If you paste dates into the range, validation may be bypassed. Use Paste Special → Values to avoid carrying over formatting that can mess with the rule.
• Existing data isn’t checked. Data validation only affects entries made after the rule is applied. To find existing weekend dates, use a helper column with the formula =WEEKDAY(A1,2) and filter for 6 or 7.
• Dates are stored as text. If a cell contains “2024-01-15â€� as text, WEEKDAY returns #VALUE!. Use the Error Checking feature or convert text to dates with DATEVALUE.
• Copying the rule to other cells. When you copy a cell with validation, the formula adjusts relative to the new location. If you need an absolute reference, use $A$1.

Alternatives: Drop-Down Lists and Conditional Formatting

If you prefer a non-formula approach, you can create a drop-down list of weekday names instead of validating a date. Use Data Validation → List and enter Monday,Tuesday,Wednesday,Thursday,Friday as the source. This restricts entries to those exact text strings, but it doesn’t check whether a typed date is a weekday.

Conditional formatting is a visual alternative: you can highlight weekend dates in red without preventing entry. Use a formula rule like =WEEKDAY(A1,2)>5 and set a fill color. This is useful when you want to see at a glance which dates are weekends but still allow them for data entry.

Does It Work in Google Sheets?

Yes, the same formula works in Google Sheets. The steps are similar: select the range, go to Data → Data validation, choose Custom formula is, and enter =WEEKDAY(A1,2)<=5. Google Sheets also has a built-in “Dateâ€� validation, but it doesn’t have a “weekday onlyâ€� option, so the custom formula is the way to go.

One difference: in Google Sheets, the error alert style is set in the same dialog, and you can choose “Reject input� or “Show warning.� The formula syntax is identical to Excel’s.

Frequently Asked Questions

Can I allow only weekdays but also allow a specific holiday?

Yes. Use an OR function to add exceptions. For example, if you want to allow Monday through Friday plus January 1st, use =OR(WEEKDAY(A1,2)<=5, A1=DATE(2025,1,1)). You can extend this with more OR conditions or a named range of holiday dates.

What if I want to block only Sundays, not Saturdays?

Use =WEEKDAY(A1,2)<=6. That allows Monday through Saturday (1–6) and rejects Sunday (7). If you want to block only Saturdays, use =WEEKDAY(A1,2)<>6 (allows everything except 6).

Does data validation work on dates entered via a form or imported data?

Data validation applies to manual entry and paste. If you import data using Power Query or a VBA script, validation may not run automatically. You can re-apply validation after import, or use a VBA macro to check the dates.

Why is my formula rejected when I type it into the validation box?

Excel requires the formula to start with an equals sign. If you copy from a cell that has the formula, it might include a leading apostrophe or spaces. Also, ensure you’re using the correct cell reference for the top-left cell of the selected range.

Can I use this validation in a table column?

Yes, but you need to apply the rule to the entire column. Select the column (e.g., D:D) and enter the formula with the first data cell, like =WEEKDAY(D2,2)<=5. Excel will automatically adjust for each row. Be aware that new rows added to the table will inherit the validation.

What does the error message look like by default?

By default, Excel shows a red “Stop� alert with the message “This value doesn’t match the data validation restrictions defined for this cell.� You can customize this in the Error Alert tab of the Data Validation dialog, including the title and text.

Related

• Convert Images to 3D: How It Works & Best Tools
• Storage and NAS: What You Need to Know
• Do Smartphones Reduce Brain Power? Facts

in d | Enter a date | - | 2024-01-15 | 2000-01-01 | 2100-12-31
out is_weekday | Weekday? (1=yes) | - | (WEEKDAY(d,2)