The situation

We need to input certain data on a daily basis.  For example, daily sales.  That is supposed to be an easy and routine task for many people.  BUT… it happened that we still have a chance to input data to a wrong date, e.g. tomorrow… like below:

Seriously?  We need to worry about and protect our spreadsheet for such a simple task?

Yes.  Why not?  Indeed not many people know we can do that in Excel.  It is quite simple if you know how to use Data Validation – Custom.

Avoid mistaken input to future days

To avoid that, we may set a simple Data Validation to the cell range B2:B31:

Select B2:B31 –> Go to Data –> Data Validation

1. On Settings tab –> Allow: Custom
2. In the Formula, input the following

=A2<=TODAY()  'Make sure the active cell is B2

Step 3 is optional, but recommended… you should tell your user why his/her input is restricted although most of them just “ignore” the popup error message. 😛

In-cell dropdown by Data Validation – Allow input NOT from a list

What Data Validation – Custom does?

Simply put, whenever you input anything in a cell with custom data validation, Excel evaluates the formula (condition) set, and only let your input go through if the condition is met.  In our example, the condition means “When the cell on the left (A2) is today or before (<=Today())”.

As simple as this.  🙂