Excel incorrectly interpreting dates

[kasper]

I am limited in my work environment as to what I can change, but I'm having a serious issue with my spreadsheets incorrectly interpreting dates. I noticed it when dealing with it in this thread http://www.mrexcel.com/forum/excel-questions/854863-find-date-list-return-static-cell-value.html, but didn't think too much about it. Now I've actually seen that when I enter May 29 (today's date), Excel returns 1-May in the cell and 01/05/2029 in the entry bar.
When I go in to custom to rearrange the date the way I want (mmm-dd), I see that the month I enter is being interpreted by Excel as the year and vice versa.
How do I fix this? It's driving me mad!!

 

[steve the fish]

Just enter 29 May instead.

 

[kasper]

Okay thanks that works, but have you seen my issue before? If I enter May 29, excel interprets it as May 01, 2029. Am I doing it wrong, or is Excel being wonky?

 

[Joe4]

It may have to do with your Regional Settings.
See: Regional Date Formats in Excel | Excel Semi-Pro

 

[steve the fish]

I always thought that was default behaviour but im in UK so may be different elsewhere. Always dd-mmm here. If you put the 'day' after the month it is interpreted as 1930-2029 by default. ie Jan 30 would be 01/01/1930 but Jan 29 would be 01/01/2029.

 

[Joe4]

Here in the US, if I enter "May 29" in a cell, it interprets it as the 29th day of May for the current year (2015).
So the behavior is definitely different depending on where you are and what your Regional Settings are.

 

[kasper]

Thanks for the info gentlemen. I'm in Canada, but I don't have access to check anything in the control panel as work as us pretty locked down. I expected Excel to behave as Joe4's does, but clearly it's acting more like steve the fish's. Good information. Will be checking my Regional Settings at home to see how things behave.

 

[deletedalien]

This DOES have a lot to do with Regional settings. i'm in Mexico and originally had the Spanish version of excel 2013 and only lasted about 2 days on my computer for this reason, now i have regular excel in English,

(i learned in excel using the English version and can't stand the spanish version.... and i'm mexican! hahahaha)