Excel Data Validation for dates changes date format

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote