Excel Data Validation for Date Format (4 Suitable Examples)

We use Excel in our day-to-day life. One important aspect while using Excel is that we need to use date and time every now and then. You may have some specified dates that you need to use in the worksheet. Here comes Excel Data Validation for Date Format to ease the task for you. In fact, this is a super powerful tool that you can use to validate your date-type data. And I’m going to show you how to do so in this article. So to know every detail, follow along with this article. To get an overview of what we’re going to do in this entire article, you may check the following image.

What Is Data Validation in Date Format?

Data validation in Date Format is a feature of Excel. This allows you to control the type of data one enters into a cell. With data validation, you can specify that a cell can only accept values that meet the criteria. So, when we’re setting date format as data validation, For example, if you have a column that should take only date as input, you may use data validation in date format. As a result, it’ll only accept date-type data. And show an error message otherwise. In short, this is the idea of data validation.

Excel Data Validation for Date Format (4 Suitable Examples)

In this article, I’m going to demonstrate to you 4 easy and effective methods to validate data in date format. Our methods include VBA as well. To get a view of what our dataset looks like, please follow this image.

This is a dataset of Order Details. We have Order ID, Customer Name, Order Date, and Delivery Date in our dataset.

1. Creating Dynamic Drop Down List of Dates

In this method, we can validate the date format by creating a dynamic drop-down list of dates. For example, in our dataset, the Delivery Date depends on the Order Date. The Delivery Date should be within 3 days from the Order Date. As the Order Date is 4/1/2023 which means April 1st, 2023, the possible Delivery Date can be any day within the 3-day time frame including the Order Date.

Steps:

When we enter the Delivery Date, it should show us only the possible delivery date options. We can do so by following the steps I’ll mention now.

• Type the following formula in cell C13 to populate the Order Date from cell D6 and press ENTER. I’ve shown the formula in cell D13 using the FORMULATEXT function for your convenience.

• Now, type the following formula in cell C14 to get the first possible Delivery Date and press ENTER.

• Similarly, get the next two possible Delivery Date by using the =C13+1 formula in cell C14 and the =C13+2 formula in cell C15 respectively.

Hence, we’ll get all the possible Delivery Dates within 3 days from the Order Date including the Order Date.

• Select cells ranging from C14 to C16 and type DeliveryDate in the Name Box.

• Now select cells E6:E11 to add Data Validation. Go to Data>>Data Validation.

• Select List from the Validation Criteria.

• Now, place the cursor on the Source field and press F3. This will show up the Paste Name option.

• Select the DeliveryDate name and click OK. This will set DeliveryDate as the Source.

• Now click on OK to select DeliveryDate as the source and set the Data Validation.

Now our Data Validation is all set. Watch the following video to see how it works.

2. Using Date Option to Validate Data Within an Interval of Date

We can do the task in another method. We can set Date as the Allow type of the Data Validation.

Steps:

• Select cells B13:C14 and go to Formulas>>Create from Selection>>Left Column and click on OK.

• Select cells E6:E11 for data validation. Go to Data>>Data Validation.

• Type =FirstDate as Start date: and =LastDate as End date: in the fields exactly as it appears in cells B13 and B14 Finally, click on OK.

Now the Data Validation is set. This means the cells E6:E11 will only allow a date between 4/1/2023 that is FirstDate and 4/3/2023 which is LastDate as valid input. It’ll show an error message otherwise. Check the following video to get a glimpse of how it works.

3. Validating Date Type Data Using Custom Option/(Restrict Date to Current Year)

We can also use the Custom option of the Data Validation to restrict our Delivery Date within the 3 days from the Order Date. That means, if the Order Date is 4/1/2023, then the possible Delivery Dates will be 4/1/2023, 4/2/2023, and 4/3/2023. If we enter any other date in the Delivery Date column, it’ll show an error message.

Steps:

• Select cell E6 and go to Data>>Data Validation to add data validation to the cell.

• Select Custom as Allow type, type =AND(E6>=D6,E6<=D6+2) as the Formula and click on OK.

• As a result, the data validation formula is now active on cell E6. To apply it to cells E6:E11, click and drag the AutoFill handle to cells E11.

Now, the data validation is set to all the cells in the range E6:E11. Watch the following video to check how it works.

4. Incorporating VBA to Validate Date Type Data in Cells

In this method, I’ll show you how you can validate the date format using VBA. That is, we’ll restrict the data type of certain cells to dates. Excel will show an error message if someone tries to input any type of data other than the date on those cells.

Steps:

• Right-click on the worksheet named Using VBA to Validate Date and click on the View Code option.

• As a result, a code window will open.

• Write the following code in the code window and click on the Save button. After that, close the code window.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
' Disable events temporarily to prevent recursive triggering
Application.EnableEvents = False
' Check if the modified cell is within the target range
If Intersect(Target, Me.Range("E6:E11")) Is Nothing Then Exit Sub
' Check if the cell value is a valid date
If Not IsDate(Target.Value) Then
' Undo the cell change and show an error message
Application.Undo
MsgBox "This cell takes only date as input. Please provide proper input."
End If
' Re-enable events
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
                        Application.EnableEvents = False

If Intersect(Target, Me.Range("E6:E11")) Is Nothing Then Exit Sub

If Not IsDate(Target.Value) Then
                        Application.Undo
MsgBox "This cell takes only date as input. Please provide proper input."
End If

Application.EnableEvents = True

End Sub

Now, we can only input Date type data in cells E6:E11. If we do otherwise an error message will pop up. Check the following video to watch what happens here.

How to Use Time Option to Validate Data Within an Interval of Time in Excel

You can use the Time option to validate data within an interval of time. The dataset looks like this.

We need to enter the Order Time in cells E6:E11. Note that the valid Order Time is between 9:00 AM to 5:00 PM.

We do so, first, by selecting the cells E6:E11 and going to Data>>Data Validation.

• Select Time as Allow option, between as Data, 9:00:00 AM as Start time, and 5:00:00 PM as End time. Finally, click on OK.

Now, time is set and we can only input time between the specified time. Otherwise, an error message will pop up. Check the following video to see what is happening here.

Takeaways from This Article

If you’ve followed this article properly, we should be now able to:

• Set Date format as a validation criterion.
• Use different options of Data Validation according to your need.
• Use the Time Option as a validation criterion.

Things to Remember

While working on the date format of data validation you should keep in mind some notable points.

• Keep a one-word name if you need to refer to it further.

•  Date accepts / and – as separator mostly. In general, it doesn’t accept “.” as a separator.

Download Practice Workbook

You can download our practice workbook from here for free!

Conclusion

In the end, we can say that we’ve covered all the necessary topics that you may require to work on Excel data validation with date format. I’ve shown different ways to achieve this. I’ve covered the time format too. I hope, with some practice, you can now use these in your real life. I’ve focused on covering each and every topic as simply as possible. Having said that, if you face any problem implementing this, or have any questions in general regarding this article, please let us know in the comment section. We’ll try our best to solve your Excel problems. Have a good day!

Related Articles

• How to Perform Data Validation for Alphanumeric Only in Excel
• How to Use Data Validation in Excel with Color
• How to Circle Invalid Data in Excel
• How to Create Data Validation with Checkbox Control in Excel
• [Fixed] Data Validation Not Working for Copy Paste in Excel
• Excel Data Validation Greyed Out

<< Go Back to Data Validation in Excel | Learn Excel