In this article, we are going to show you how to allow users to enter date values only for the current week in Excel.
To limit the user interaction with input cells, you can define validations. You might be familiar with data validation from dropdown input styles. In addition to dropdown style lists, you can also limit free-form data entry using data validation. Let’s see how you can set a validation to enter dates in the current week only in Excel.
- Select the cell(s) you want to be the date inputs.
- Activate the Data tab in the ribbon
- Click the Data Validation
- In the Settings tab, set Allow as
- Set Data to between
- Enter Start Date =TODAY()-WEEKDAY(TODAY(),3)
- Enter End Date =TODAY()-WEEKDAY(TODAY(),3)+6
- (Optional) Activate the Error Alert tab and modify the error message.
- Click the OK button to apply the validation.
Once the validation is set; entering any date outside of the current week will produce a dialog window and prevent data entry.
Retry: Returns to the cell with invalid value.
Cancel: Removes the invalid value from the cell.
The formula to enter dates in the current week
Although you can enter an actual date into the Start Date and End Date fields in the Data Validation dialog, using a formula ensures that the dates are populated correctly. For example, the current week is different for two users which opens the workbook at 01/15/2021 and 05/27/2023.
To calculate the first and last day of a week, all you need to do is combine the TODAY and WEEKDAY functions. The following formulas return the dates for the Monday and Sunday of the week respectively:
Error Alert
Please note that in Error Style options, any other option than Stop will allow end users to enter an invalid value. These options are good if you want to keep your validation as an informative warning message instead. Otherwise, leave it as Stop to prevent invalid data entry.
