Data Validation to restrict user from inputting to future days

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:

Excel Tips - Data Validation to restrict input to future days

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

Excel Tip - Data Validation to restrict input to fututre days

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. 😛

Excel Tip - Data Validation to restrict input to fututre days 1In-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.  🙂

Advertisements
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

2 Responses to Data Validation to restrict user from inputting to future days

  1. motherborg says:

    this is actually great tip, thank you!

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s