How To Validate Date Input In Excel

When a user inputs a date or time value in an Excel cell, you may want to validate the user input value to check whether the date or time value is correct or not. And when you open an existing worksheet that contains a lot of date-type values, you may want to validate those existing date values and point out those date values that are not valid. This article will tell you how to implement these with examples.

1. How To Use Excel Data Validation Tool To Restrict Date Input In Excel Example.

  1. Open an excel worksheet.
  2. Input text Start Date in excel cell A1.
  3. Then select cell B1, click the Home tab, and select the Short Date item from the Number Format drop-down list in the Number group.
  4. Input a date value such as 10/1/22 in cell B1.
  5. Select a target cell such as cell C1.
  6. Click the Data tab —> Data Validation item in the Data Tools group to open the excel Data Validation dialog window.
  7. Click the Settings tab, and select the item Date from the Allow drop-down list.
  8. Select the item greater than from the Data drop-down list.
  9. Input the formula =$B$1 in the Start date input text box.
  10. Click the Input Message tab, input the text Input A Valid Date in the Title text box, input the text Please input a valid date value. in the Input message text area.
  11. Click the Error Alert tab, and select Stop from the Style drop-down list. Input text Invalidate Date in the Title text box, and input text The date value should be greater than the date value in cell B1. in the Error message text area.
  12. Click the OK button to create the rule.
  13. When you select cell C1, it will show a yellow tip message with the title Input A Valid Date and the message Please input a valid date value.
  14.  And when you input a date value in cell C1 and the date value ( such as 10/1/21 ) is less than the date value in cell B1.
  15. Then it will show an alert dialog with the title Invalidate Date and the message The date value should be greater than the date value in cell B1.

2. How To Validate Existing Date Value Based On Today Date Example.

  1. In example 1, we create a data validation rule first and then use the rule to validate the user input date value.
  2. In this example, we will create a rule to validate some existing date values, and only the existing date values between today and the week after today are valid.
  3. We will also circle out the invalid date value cells in the worksheet using the red circle.
  4. Open an excel worksheet and input the below data in the table.
    A
    1 9/30/22
    2 10/1/22
    3 10/2/22
    4 10/3/22
    5 9/29/22
    6 10/1/21
    7 10/8/22
  5. Select the above existing cell range.
  6. Click the Data tab —> Data Validation menu item in the Data Tools group.
  7. Click the Settings tab in the popup Data Validation dialog window.
  8. Select Date from the Allow drop-down list.
  9. Select between from the Data drop-down list.
  10. Input the formula =TODAY() in the Start date input text box.
  11. Input the formula =TODAY() + 6 in the End date input text box.
  12. You can customize the Input Message and Error Alert titles and messages.
  13. Click the OK button to create the rule.
  14. Then you can see a small green triangle on the top left corner of each cell that contains an invalid date value ( that is not between today and today + 6 ).
  15. Select one cell in the above cell range, then click the Data tab —> Data Tools group —> Data Validation —> Circle Invalid Data menu item.
  16. Then you can see the red circle on the excel cells that contains an invalid date value.
    circle-invalid-date-value-cells
  17. If you want to clear the red validation circles, you can click the Data tab —> Data Tool group —> An drop-down arrow beside the Data Validation item —> Clear Validation Circles item.

3. How To Validate Time Value Based On The Current Time.

  1. Besides Date validation, you can also create a data validation rule to validate user input time value in excel cells.
  2. Open the excel Data Validation dialog window.
  3. Click the Settings tab, and select Time from the Allow drop-down list.
  4. Select greater than from the Data drop-down list.
  5. Input the formula =NOW() in the Start time input text box to validate the date and time value based on the current date time.
  6. Input the formula =TIME( HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) in the Start time input text box to validate time value based on the current time only.

4. How To Create Custom Data Validation Rule To Valid Date Value Between 2 Dates.

  1. When you select Date from the Allow drop-down list in the Data Validation dialog Settings tab, you can find a lot of built-in operators from the Data drop-down list below it.
  2. The operators include between, not between, equal to, not equal to, greater than .etc.
  3. But if you find the above operators are not enough for your requirement, you can customize your own data validation rule using the formula.
  4. Select Custom from the Allow drop-down list.
  5. Then input the formula like =AND(C1>=DATE(2022,10,1),C1<=DATE(2022,10,31)), this formula will allow date value only in month October of year 2022.
  6. If the start date value and the end date value are saved in 2 excel cells A1 and B1, then you can input the formula =AND(C1>=$A$1, C1<=$B$1).

5. How To Restrict Date Value To Workday Or Weekend Only Using Custom Data Validation Rule.

  1. If you want to allow users to input workday or weekend only in excel cells, you can use the excel WEEKDAY function to create a custom data validation rule.
  2. Open the Data Validation dialog window as above.
  3. Click the Settings tab, and select Custom from the Allow drop-down list.
  4. If you want to allow input workday only in the cell, then input the formula like =WEEKDAY(C1,2)<6 in the formula text box.
  5. If you want to restrict the cell date value to the weekend day only, then input the formula like =WEEKDAY(C1,2)>5 in the formula text box.

Subscribe to learn more excel tricks.

We don’t spam!

close

Subscribe to learn more excel tricks.

We don’t spam!

0 0 votes
Article Rating
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x