+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    05-31-2022
    Location
    Montreal
    MS-Off Ver
    2011
    Posts
    3

    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!

  2. #2
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    423

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

    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.

  3. #3
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

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

    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.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,674

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

    Suppose on December 11th, 2023, if the user inputs 2023-11-12, it is also valid, however, it is incorrect.
    Therefore, I agree with Akuini that it should be entered in the format yyyy-mmm-dd to avoid mistakes: 2023-Nov-12
    or, in the format dd-mmm-yyyy as 12-Nov-2023
    Quang PT

  5. #5
    Registered User
    Join Date
    05-31-2022
    Location
    Montreal
    MS-Off Ver
    2011
    Posts
    3

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

    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.

  6. #6
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

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

    Quote Originally Posted by browniiesx View Post
    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".

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Format Date In A Column To mm/dd/yyyy
    By jadepu2010 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2020, 12:31 PM
  2. Update Date Field From User Form in dd/mm/yyyy format instead of mm/dd/yyyy format
    By LUCKY92 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-18-2020, 12:11 PM
  3. [SOLVED] set Data validation in input box to accept only date format mmm-yyyy
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2019, 09:49 AM
  4. Replies: 3
    Last Post: 04-25-2019, 11:05 AM
  5. Convert Date format from Text format reading m/d/yyyy to dd/mm/yyyy
    By bridge4444 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2017, 11:22 PM
  6. [SOLVED] Imported data contains strings dd.mm.yyyy how can I convert to date format dd/mm/yyyy inVB
    By Boormo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-05-2012, 05:48 PM
  7. Column Range with date Format MM/DD/YYYY independate of sys date Format
    By gaursh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2011, 03:31 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1