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]

Yes, I can do that. Thanks!

You are welcome.

Usually, I like to understand why certain things are happening, but every so often, one comes along that I cannot just figure out.
At that point, it is more important to me to get it working right, even if I cannot understand why it is behaving the way it is.
If was created by someone else long ago on a different system, who knows what other factors could be affecting it (I think we identified most of the most likely causes).

 

[Ingrid_SM]

You could also either post a screenshot of the code module for the sheet in question, or remove any data and put the workbook itself on a sharing site like OneDrive or Dropbox for us to have a look at.

I put the file in OneDrive, for you guys to have a look.

https://dayco-my.sharepoint.com/❌/g...NGrtA0sjO4iwEBqVqliVPI73066oIWpqxS-w?e=KU8uA6

 

[Ingrid_SM]

You are welcome.

Usually, I like to understand why certain things are happening, but every so often, one comes along that I cannot just figure out.
At that point, it is more important to me to get it working right, even if I cannot understand why it is behaving the way it is.
If was created by someone else long ago on a different system, who knows what other factors could be affecting it (I think we identified most of the most likely causes).

I totally agree. I also need to know why things happen that way, but sometimes I have to quit, specially if I'm involving other people that do the work for me!
Thanks to everybody again <3

 

[RoryA]

I put the file in OneDrive, for you guys to have a look.

https://dayco-my.sharepoint.com/❌/g...NGrtA0sjO4iwEBqVqliVPI73066oIWpqxS-w?e=KU8uA6

That link doesn't work for me. Possibly because of the big X icon and possibly because it's not public?

 

[Ingrid_SM]

https://dayco-my.sharepoint.com/❌/g...YjmYDqQuvNGrtA0sjO4iwEBqVqliVPI73066oIWpqxS-w

That link doesn't work for me. Possibly because of the big X icon and possibly because it's not public?

Sorry, maybe it is not public... Can you try this one?

WD_S503_REGISTRE CONSUM PLAQUETES_beta4.xlsm

Shared with Dropbox

www.dropbox.com

Thanks

 

[Alex Blakenburg]

Hello Ingrid,

In the project window (in the left pane) double click on Hoja3 (aka ENTRADA DADES S-503).
In the code window on the right replace the code with any of the options given by Rory.

Just changing the UCase to Value2 will fix it but it does make sense to use Rory's code and totally skip the UCase code if it is a number.

 

[Ingrid_SM]

Hello Ingrid,

In the project window (in the left pane) double click on Hoja3 (aka ENTRADA DADES S-503).
In the code window on the right replace the code with any of the options given by Rory.

Just changing the UCase to Value2 will fix it but it does make sense to use Rory's code and totally skip the UCase code if it is a number.

It works!!!!

OMG! I've been changing the code all the time on the Modules part, not on the Sheets part...! I guess that's why your fixings didn't work... I'm sorry I didn't know that.

I'm amazed at your knowledge and very thankful for all your time and help.

Thanks and have a great weekend!

Ingrid

 

[Alex Blakenburg]

Thank you for being prepared see it through to a resolution.
It would have left us wondering if we didn't get it fixed for you.

Worksheet events only trigger if they are in a "sheet" module or in the ThisWorkbook module, that trying to uppercase a date could cause that to happen was new to us too.

Glad we could help and have a great weekend (it is already Saturday here in Australia)

 

[Ingrid_SM]

Thank you for being prepared see it through to a resolution.
It would have left us wondering if we didn't get it fixed for you.

Worksheet events only trigger if they are in a "sheet" module or in the ThisWorkbook module, that trying to uppercase a date could cause that to happen was new to us too.

Glad we could help and have a great weekend (it is already Saturday here in Australia)

Thanks for your teachings!
Have a great week!