1

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:

Created with 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:

Opened with Excel 365 with 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?

Worthwelle
  • 4,538
  • 11
  • 21
  • 32
schluk5
  • 11
  • 2
  • Does this answer your question? [How to prevent Excel to use the OS regional settings for date patterns in formulas](https://superuser.com/questions/730371/how-to-prevent-excel-to-use-the-os-regional-settings-for-date-patterns-in-formul) – Saaru Lindestøkke Aug 13 '20 at 14:20

2 Answers2

0

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...:
    enter image description here
  3. Go to the Custom category and type in the desired format in the Type: field:
    enter image description here

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

enter image description here

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.

Saaru Lindestøkke
  • 5,515
  • 8
  • 30
  • 48
  • That is excatly what I tried and it looks like custom format eg "yyyy-mm-dd" does not survive a different locale. So frustrating! :-) – schluk5 Aug 12 '20 at 16:39
  • @schluk5 ok, it was entirely not clear from your question that you've tried this already. What happens with the dummy `dd$mm€yyyy` custom format? Does it also simply override that? – Saaru Lindestøkke Aug 12 '20 at 16:42
  • This one actually is not changed. So it must have something to do with '-' beeing transformed to '.' for whatever reason. Do you know of a way to force the '-' with the custom formatting? – schluk5 Aug 13 '20 at 13:59
0

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.

schluk5
  • 11
  • 2