Issues with Regional Date formatting

Ignoring changing PC regional settings or Excel date settings I just want to understand the basics.

You copy in a date that is in M/d/yyyy e.g. 12/25/19

Are you saying the result you want is for your spreadsheet to not only display 2019-12-25 but to actually change the data to be 2019-12-25 as well OR can the data itself stay as 12/25/19.

Thanks for the reply. I would be happy with either option, frankly. As long as I can use the "12/25/2019" as a date, I'm happy. But I get a string that can't be parsed as a date, meaning that all date functions return "#VALUE!".

Originally Posted by popipipo

Is changing the celformat to yyyy-MM-dd not enough?

As I have stated earlier, a "M/d/yyyy" string is not understood as a date. So no, just changing the cell format doesn't change the displayed text.

Hi Buggaby

You could use formulas in another cell to display the data in your format once its been pasted but this would be clunky and not recognised as a date for sorting or display.

If you were importing data instead of just copy/paste you could use text to columns to manipulate the data as it is imported but I really don't think you can do what you want as stated as Excel would almost need to be psychic to display what you want and trteat it as a date in 2 different formats (incoming and display).

Is there a reason you need the YYYY format apart from just being comfortable with that? Sorting or some other reason? If not I don't see how this can be done without coding or import.

I'm not sure I understand why Excel would need to be "psychic". I mean, Google Sheets does this properly.

And more than that, Excel already does interpret multiple formats. Specifically, as pointed out in the question, it interprets yyyy-MM-dd fine regardless of the regional settings. It's only the M/d/yyyy that is treated so uniquely for some reason.