Excel date format changes with different regional settings (2020-08-01 to 2020.08.01)

I searched most of the posts, but there is no solution to my problem.

This is what I tried using Excel 365 with English Windows Machine:

And this is what I get when I open the file with Excel 365 on a German Windows Machine:

I thought removing the * from a formatting forces that format, no matter which region you open it in?

As you can see I tried other things too, but none works.

Any idea?

2 Answers

You can use Custom formatting to format the date however you like.

1. Select the data you want to format
2. Go to Home ➡ Number ➡ More Number Formats...:
   
3. Go to the Custom category and type in the desired format in the Type: field:
   

If I for example type dd-mm-yyyy I get to see 01-08-2020. Other examples are shown below:

_{I believe the custom format survives locale formatting, but I don't have a machine with a different locale to test and am to lazy to switch on this machine.}

The only way I found now is to format that specific cells as text and then reenter the dates eg 2020-08-01. This works so far, but you need to work with =TEXT(,) formula with the right formatting eg =TEXT(, "yyyy-mm-dd"), when you want to refer to that cell.