Only accept a date format of yyyy-mm-dd in one column

Hello everyone!

First, I'm a french speaker - sorry for my poor english.

I'm working in a marketing company and we have one mandate with a client to follow specific events that their customers do each month. We have two sources for this: one from another team, where they put what they have in a shared Excel, and another from our website, where the customers put the information themselves. At the end of every month, my team compare the information with our website database to see if there's any differences in the inputs of both sources. We developed a Query that compares easily both sources to get it done fast. In the shared Excel, the dates of each event are put in column H: that's where we have a problem.

See, in the website database, all dates are in this one format: yyyy-mm-dd. The other team, though, puts different formats for each event every month. It can be dd-mm-yyyy, mm/dd/yyyy, yyyy-dd-mm, etc.

With that, our query cannot make the comparison perfectly. We have to compare them manually, which is long work, and sometimes we miss errors before sending the report to our client.

Now, we tried to teach them to only use yyyy-mm-dd as a format, but it doesn't stick. So, I'm looking for a solution where the column could only accept one date format (yyyy-mm-dd), and if they try to put anything else, there's a message error.

Just to say: I tried data validation, but since they copy/paste their information, it doesn't work. I tried a VBA that forces the data validation to works even if it's copy/paste, but to no avail. Here it is:

Please Login or Register  to view this content.

I would prefer a VBA that do the data validation itself: only accepts one date format, no matter if it's a copy/paste, insert, etc., but didn't find any solution online. If someone can help, please do!


Thank you!

I think this should work, but only if the range where the dates are in is formatted as Text. If it is formatted as General or as 'yyyy-mm-dd', Excel will try to interpret whatever is entered and you'll hit the problem where the days and months get reversed with no way of knowing.

Please Login or Register  to view this content.

If you format the range as yyyy-mm-dd, then any date inserted by the user in any format will be automatically converted to yyyy-mm-dd.
Also, I would suggest using the format with the month's name, i.e., yyyy-mmm-dd, for example, '2024-Mar-02'. This will help minimize confusion.

The problem is, the people inserting those dates are speaking different languages, and so their Excel is programmed in a different one. A date like 13/02/2024 is not read as a date on our French Excel, only text. Thus the errors.

Originally Posted by browniiesx

The problem is, the people inserting those dates are speaking different languages, and so their Excel is programmed in a different one. A date like 13/02/2024 is not read as a date on our French Excel, only text. Thus the errors.

Just to say: I tried data validation, but since they copy/paste their information, it doesn't work.

Try this:
The code will check 2 things:
1. The cell must be formatted as "yyyy-mm-dd", If it is not, then the code will apply the format to the cell in this part:

Please Login or Register  to view this content.

2. The cell text must be Like "####-##-##", see this part: 'Not rngCell.Text Like "####-##-##"', notice that I'm using .Text instead of .Value.

Please Login or Register  to view this content.

Notes:
1. If a user inputs a date by typing in the cell, any date format is allowed, but it will be automatically converted to yyyy-mm-dd.
2. If a user inputs a date by copy-paste:

• If it is pasted as a value, then any date format is allowed, but it will be automatically converted to yyyy-mm-dd.
• If it is pasted as All, then it will be rejected if the format isn't "yyyy-mm-dd".