Categories
Excel

Data Validation: Prevent Weekend Entries

In this tutorial, we’ll discuss how to prevent weekend entries to restrict the input of dates that fall on Saturdays or Sundays. This can be useful for applications such as scheduling, booking, or reporting that only operate on weekdays.

In Excel, data validation is a process of ensuring that the data entered in a spreadsheet meets certain criteria and conforms to the expected format. To use data validation, follow these steps:

  1. Select the cell or range of cells where you want to prevent weekend dates.
  2. Go to the Data tab and click on Data Validation in the Data Tools group.
  3. In the Data Validation dialog box, select Custom from the Allow drop-down list.
  4. In the Formula box enter the formula =WEEKDAY(A2:A10,2) < 6.
  5. Click the Error Alert tab, enter a Title and Error Message.
  6. Click OK to apply the data validation.

The formula =WEEKDAY(A2:A10,2) < 6 is used to check if the dates in the range A2:A10 are weekdays or not.

Now, when you try to enter a weekend date in the cell or range of cells, you will get an error message saying that the value you entered is not valid.

Accept Dates by the Day of the Week

The WEEKDAY function in Excel returns the day of the week for any given date. To use this function, you need to provide at least one argument: serial_number, which is the date you want to analyze. It returns a number from 1 (for Sunday) to 7 (for Saturday), for example, the =WEEKDAY("2023-06-14") returns 4, which means Wednesday.

For example, to allow only Wednesday date, replace the =WEEKDAY(A2:A10,2) < 6 formula (see step-4 on the previous listing) with this formula:

=WEEKDAY(A2:A10) = 4
  1. Allow Sunday
    =WEEKDAY(A2:A10) = 1
  2. Allow Mondy
    =WEEKDAY(A2:A10) = 2
  3. Allow Tuesday
    =WEEKDAY(A2:A10) = 3
  4. Allow Wednesday
    =WEEKDAY(A2:A10) = 4
  5. Allow Thursday
    =WEEKDAY(A2:A10) = 5
  6. Allow Friday
    =WEEKDAY(A2:A10) = 6
  7. Allow Saturday
    =WEEKDAY(cell_range) = 7

Data Validation