How to apply date controls in data validation in Excel

Follow this step by step tutorial to learn how to apply date, time or custom controls in Data Validation in Microsoft Excel

Don't forget to check out our site howtech.tv/ for more free how-to videos!
youtube.com/ithowtovids - our feed
facebook.com/howtechtv - join us on facebook
plus.google.com/103440382717658277879 - our group in Google+

Microsoft Excel is a detailed software that can be used as a multiple analysis tool. It gives simple options that can help you manage and manipulate data to derive desired or actual results. It is important to understand the workings of this software to be able to use it effectively. Data Validation in Excel can help create criteria for specific results. Date Data Validation is a way of bringing output as per your set rules. There are options of Time or Custom Data Validation too. Follow this step by step tutorial to learn how to apply date controls in Data Validation in Excel.

Step # 1 -- Select 'Data Validation'

First of all, select the entire column and move to the "Data Tools" section in the ribbon on top. From there, select the "Data Validation" in Excel.

Step # 2 -- Enter Dates

Once the "Data Validation" window opens up, choose "Date" for the "Allow" option.
Now enter the "Start date" and the "End date" and click on the "OK" button to complete the Date Data Validation.

Step # 3 -- Enter Reminder in 'Input Message'

Open up the "Data Validation" button again and move over to the "Input Message" tab. Over here you can add a reminder so that a message is displayed every time you hover over the cell or column.

Step # 4 -- Enter Time data

Once again go to the "Data Validation" window and this time select "Time" instead of "Date" for the "Allow" option. Enter the "Start Time" and the "End time". We have entered 9 hours for the start time and 20 hours for the end time. What this means is that any time before nine am and any time after eight pm can't be entered in the sheet. Press the "OK" button and try entering random values in the column.

Step # 5 -- Use the Custom data option

In column "F" we have "Receiving Dates". Let's suppose it takes at least two days for the goods to be shipped so we want to enter a function which will only allow such values to be entered which are two days after the shipping date. The procedure is the same however this time instead of choosing "Date" or "Time" we will select "Custom" and then write in a "Formula".
For that, type:

=f1 more than e1+2

Once done, click on the "OK" button. Now if you try to enter a date which is less than two days from the shipping date, it won't be entered.