Excel Data Validation for Date Format (4 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of Excel data validation date format

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.

Image of dataset

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.


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.

Populating Order Date in cell C13

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

Get first possible Delivery Date

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

Get all the possible delivery dates

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.

Editing Name Box for Delivery Dates

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

Navigate to Data Validation

  • Select List from the Validation Criteria.

Selection of List as Validation Criteria

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

Paste Name option showing up

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

Source Selection

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

Data Validation OK

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.


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

Create from Selection Settings

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

Navigate to 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.

Set start and end date

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.


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

Add data validation to cell E6

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

Enter data validation formula

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

Apply data validation to all cells

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.


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

Opening code window

  • As a result, a code window will open.

Opening code window

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

Code in code window


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
MsgBox "This cell takes only date as input. Please provide proper input."
End If
' Re-enable events
Application.EnableEvents = True
End Sub

Code Breakdown:

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

The set of instructions indicates that the rest of the code should be executed whenever a change is made to the worksheet and temporarily disables events to prevent recursive triggering.

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

This line checks if the modified cell is within the target range (that is cells E6:E11). If the modified cell is not within this range, then the code exits and does not execute the rest of the code.

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

This set of instructions checks whether the cell is a valid date or not. If not, then the last cell change is undone to keep the active cell in the original cell where the error occurred. And shows an error message.

Application.EnableEvents = True

This statement reenables events so that future events can be triggered.

End Sub

The sub-procedure ends.

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.

Dataset of Time Validation

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.

Navigate to 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.

Setting Time validation options

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!


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hadi Ul Bashar
Hadi Ul Bashar

I'm Hadi Ul Bashar, an Engineering aspirant. I always look forward to keeping myself up to date in my area of interest. Currently, I'm happy to work at Exceldemy as an Excel & VBA Content Developer. My goal here is to provide an easy and detailed solution to whatever problem you may face in Excel. I hope I will make your Excel life easy as I'm always open to solve new problem!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF