Results 1 to 2 of 2
2014-06-05, 10:41 #1
- Join Date
- Feb 2010
- Essex, UK
- 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.
Last edited by sjreynolds143; 2014-06-05 at 10:45.
2014-06-09, 09:33 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- 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