Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Feb 2010
    Location
    Essex, UK
    Posts
    48
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Excel Data Validation for dates changes date format

    I am in the UK, and my Excel is set to UK local defaults. In particular, dates are dd/mm/yyyy.

    I am creating an Excel document where users will have to enter dates. So I want to validate that they are dates, and not too old. The only problem is, Excel (2013) treats (new) dates as being in US format as soon as I apply validation. Why? What possible reason can it have for doing that! And how can I stop it!

    Now I am no Excel slouch but this is driving me batty! It's quite repeatable, even on a blank workbook.

    • If I format a blank cell with the format dd/mm/yy and then enter 1 sep 14 I get 01/09/14. That's correct.
    • Now enter data validation for that cell so that it has to be a date, greater than, and start date has the formula =NOW()-188
    • Finally change the value of the cell by typing 2 sep 14 and I now get 09/02/14! Totally incorrect! That no only looks like 9th Feb, but when you format it into dd-mmm-yy format you discover that it IS 9th Feb!
    • Clear validation, change date to say 3 sep 14 and lo and behold it is correctly 03/09/14.

    It's clearly the validation that is at fault. So how can I stop it doing this! It's nothing to do with my locale or keyboard settings.

    Thanks
    Stuart
    Last edited by sjreynolds143; 2014-06-05 at 11:45.

  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
    Which version of Excel are you using? I could NOT confirm this behavior in XL2010.

    What I did:
    1) Went to control panel and changed by default to ENglish UK which changed the default date format to dd/mm/yyyy
    2) Formatted a blank cell with the format dd/mm/yy and then entered 1 sep 14. I got 01/09/14 [and confirmed it was Sep 1, 2014] like you
    3) Entered data validation for that cell so that it has to be a date, greater than, and start date has the formula =NOW()-188
    4) Change the value of the cell by typing 2 sep 14 and I did NOT get 09/02/14 I got 02/09/2014 [and confirmed it was Sep 2, 2014]

    My format did not change (it remained dd/mm/yyyy), and the value entered did not change to a different date either

    Steve

Posting Permissions

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