Regional settings and date format problem

[Watersource]

Dates are stored in the cell using the regional date settings, instead of the required US date format (which I need for creating an XML file), even when I enter the date using US format.
Let me give an example:
I enter date 2020-12-31 in the cell. This is also shown in the grid as 2020-12-31 but when I look in the formula bar I see the date as 31-12-2020 (dutch format).
The cells are formatted using English (United states) date format, my pc is set up as Netherlands region (changing this to US and re-opening the sheet did not make a difference).

When I use a left formula to retrieve the year from the cell, then the value 31-1 is shown instead of the expected 2020.
The cell.png is how the value is shown (and entered)
The formula bar.png is how the value is stored
The cell properties.png is how the cell is formatted
The regional settings.png shows the regional settings.

Thanks for your help on finding how to store the dates in US format/transforming them in US format.

 

[jasonb75]

You need to enter the dates into the cell in your local format. The cell format is only visual, if you want to change the way that the date is entered then you would need to change the regional settings of your computer before entering them.

If using LEFT to retrieve the year is coming up as 31-1 then that means that it is a text string instead of a valid date so formatting will have no effect regardless of what you change.

 

[Watersource]

You need to enter the dates into the cell in your local format. The cell format is only visual, if you want to change the way that the date is entered then you would need to change the regional settings of your computer before entering them.

If using LEFT to retrieve the year is coming up as 31-1 then that means that it is a text string instead of a valid date so formatting will have no effect regardless of what you change.

Thanks for your feedbank. This explains why the date is stored and shown as it is. What would you recommend to do ensure that in the XML file I need to create based on this data, the date is stored in the US format YYYY-MM-DD?

 

[jasonb75]

From what I can see the only way to preserve formatting is to enter dates as text, either manually or by converting them with vba. If you want to convert them with vba then you would need to enter them in your local format to avoid conflicts.

To enter dates as text manually you would either need to format the cells as text or precede them with an apostrophe. This needs to be done before they are entered, with existing dates you will get the date serial number (number of days since 1/1/1900) for anything that is valid rather than the actual date.

Other than that it would need to be handled by the destination software after it has been exported.

FYI YYYY-MM-DD is an international format, not US format. Dates entered in this format are recognised by excel regardless of settings then converted to local dates. US dates are formatted as MM/DD/YYYY which can conflict with dates in some european countries. For example 1/5/2021 can be either Jan 5th or May 1st depending on location, both of which are valid dates.
Any date where the day is the 13th or later would be invalid if it was not in local format as 13 would not be recognised as a valid moth and excel would not see it as a date. Potentially resulting in a lot of errors and anomalies in your data.