Why is Excel changing the date I enter to American style?

[Ingrid_SM]

Hi there,

I have a workbook with a field to enter a date in format dd-mmm-yyyy.
If the day is lower than 13, it takes the day as the month and the month as the day and gives me the date in the same format dd-mmm-yyyy but with the date that would result in American style.
For example, I want to enter 11 (day) / 05 (month = May) / 2021. When I do enter, I get 05 (day) - November - 2021.
If I enter the date when the day is higher than 12, it does it correctly. For example: I enter 13 (day) / 05 (month = May) / 2021. It gives me 13-may-2021.
In cell format, I have: Regional configuration = Spain.

View attachment 43217

In Control Panel / Region, I have Format = Spanish too...

View attachment 43218

What's wrong?

Thanks in advance!

 

[Joe4]

Welcome to the Board!

In Control Panel / Region, I have Format = Spanish too...

Did you scroll down further in those Regional Setting to the "Regional format data" section, and update the "Short date" setting?

 

[RoryA]

If by "field" you mean a textbox in a userform, then you need to use CDate when assigning it to a cell.

 

[Ingrid_SM]

Welcome to the Board!


Did you scroll down further in those Regional Setting to the "Regional format data" section, and update the "Short date" setting?

Hi Joe4,

Thanks for the welcome and for your answer!

This is what I have there:

 

[Ingrid_SM]

If by "field" you mean a textbox in a userform, then you need to use CDate when assigning it to a cell.

No, no, sorry. By "field" I just mean a cell.

 

[Joe4]

What happens if you open a brand new workbook and try it on that?
If it works properly there, then that means that there is something with that particular existing workbook.

 

[Ingrid_SM]

On a brand new workbook, it works fine.

I copied the cell from the new workbook and pasted it on the one I have the problem with, and it keeps changing it...

 

[Alex Blakenburg]

Is the problem workbook an .xlsm or .xlsb ?

 

[Ingrid_SM]

Hi Alex,

It's an .xlsm

The problem is only in one sheet. The rest of the sheets of the workbook work fine.

 

[RoryA]

Right-click the tab for that worksheet, and choose View Code. Is there any code shown in the window that appears (probably a Worksheet_Change event routine)?