Force date validation in specified format

**valcrist_m** · 02-15-2016, 08:17 AM

Hi,

I need to validate date entry inside cells itself with format dd\mm\yyyy.
This needed as this Excel are used to import data to other system which is accepting only dd\mm\yyyy
For example:
31/12/2015 -> correct
12/31/2015 -> incorrect
31/11/2015 -> incorrect (no 31 in November)
other than date -> incorrect

Have tried some solutions on internet but none working, especially when the input is 12/31/2015.
1. Data Validation
Cannot use this approach as user might copy and paste from other source and this will remove the validation.
I also cannot restrict copy & paste as they need to copy and paste the data.

2. IsDate and DateValue
This will not work if the input is 31/11/16 or 12/31/2015
as I read on internet, the function was not specific to date format, but it will give the combination of format.

Been trying search this on google for a day but none of them can accommodate the issue.
Appreciate if anyone can help or giving idea how this should be done.
I'm using Office 2010 and the user uses Office 2013.

**dflak** · 02-16-2016, 03:49 PM

The attached uses a VBA code Change event on Sheet1. The cells to be validated are in the range A2:A4. You would have to change this range to reflect where you need validation.

I make the following checks: There are two and only two slashes in the date. The month cannot be greater than 12, the year cannot be less than 4 digits (so format the date type accordingly - months and days can be single digit), illegal dates such as 31/11/2015 won't work.

The code warns the user, clears the contents of the cell, and selects the cell again.

There is no way to check that 1/5/2015 is May 1st instead of January 5th.