How to Allow Only Date Format in Specific Cells in Excel?

Assume we want to create an excel that contains information about the dates of some important events, and everyone is attempting to enter data other than dates in the column, and you want to restrict them to only entering the date in the specified column. We can make that happen using some Excel tricks. In this tutorial, let's see a simple trick where we only allow date format data in a specified cell.

Here we will add the data validation to allow only dates. Let us look at a simple example to see how we can limit the date format to specific cells.

Step 1

Let us open an Excel sheet with the names of events and the dates of those events blank. We can enter dates after completing this simple step.

We can enter any value under the column of date, but a date can’t be any value; it has a format of dd-mm-yyyy, which specifies the particular day.

To only allow dates, we need to use the concept of data validation.

Now, on the data menu on the quick access tool bar, select data validation twice, and a pop-up will appear, as shown in the figure below.

Step 2

Now under the setting in the allow list, select Custom and enter the following formula in the Formula box −

=AND(ISNUMBER(B2),LEFT(CELL("FORMAT",B2),1)="D")

Click OK to complete the process. See the following screenshot.

In the formula, the cell "B2" represents that only date format data is allowed below the cell B2.

Step 3

Now if we try to enter any other format in the column date, an error message will be displayed.

Conclusion

In this tutorial, we used a simple example to demonstrate how we can allow only date format in Excel to highlight a particular set of data.

Pradeep Kumar

e

Updated on: 09-Jan-2023

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started