Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2011
    Posts
    284
    Thanks
    33
    Thanked 2 Times in 2 Posts

    Problems with validating dates in EXCEL

    Date fields in Excel depend on the computer's Regional settings. Different people use different regional settings that affect the way the date is displayed.

    US formats and UK / European formats are different, and validations do not work on date fields if the regional settings are different.

    Any idea on how to get around this problem?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What means are you validating? A date is a number, some of the issues is conversion of the entry into the number, not just the interpretation of the number afterwards. It doesn't matter about the display date: 2/6/2014 (m/d/yyyy) = 6/2/2014 (d/m/yyyy) = Feb 6, 2014 (mmm d, yyyy). But if my computer is set for m/d/yyyy (2/6/2014) and I enter it as d/m/yyyy (6/2/2014) excel will mistakenly convert it to the value of June 2, 2014.

    If using data-validation the conversion is done before the validation. There can be means where the validation would be done first.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2011
    Posts
    284
    Thanks
    33
    Thanked 2 Times in 2 Posts
    How do you set up a template for completion in a standard format - dd/mm/yyyy for example? It would be nice to be able to restrict how a date is captured.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I come back to my original question. How are you going to validate? Are you going to use data-validation, code in a change event, code in a userform?

    Are you planning on controlling the conversion of the date as well or allow excel to do that? Please provide some details of what you have and what you want.

    [A simple scheme in excel is to use neither mm/dd nor dd/mm but display as mmm d or d mmm, then the user can explicitly see what month excel has converted the entry to].

    The regional settings control the conversion, the display is controlled by the formatting, the data-validation is based on the value after the conversion. If you use a userform you can control the conversion. Thus I need to understand what you want and need to help you.

    Steve

    PS you remark that "validations do not work on date fields if the regional settings are different" is not a universally true statement, it depends on how you validate.
    Last edited by sdckapr; 2014-02-06 at 07:55.

Posting Permissions

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