LaVOZs

The World’s Largest Online Community for Developers

'; How do I concatenate a date cell (independant of locale) to a string in Excel? - LavOzs.Com

I have a date cell 2020-02-21.

In my mind CONCAT(A1," my date") should procude the expected string "2020-02-21 my date".

But no, Excel decides to treat is as a date and convert it to a mumbojumbo number resulting in the output "438562 my date". This would be logical if the cell value was formatted as date, but it's a TEXT field (!!).

So now you're probably going to point me to the millions of threads on this site that tell me to use the TEXT function. But here's the deal. The second argument of TEXT expects a format such as "YYYY-mm-dd" which is going to be LOCALE SPECIFIC. If I hardcode a format, the file won't work when I pass it to my collegue in spain, or whatever.

It works if I use "ÅÅÅÅ-MM-dd" but that's specific only to Sweden. I've tried the [$-xxx] codes in front of the fromat, but to no avail.

Is there a way to format the date in "YYYY-MM-DD" format without excel caring about the langauge or locale you are using?

Getting utterly frustrated that such a simple task cannot be done easily. Please help!

How about: =CONCATENATE(YEAR(A1),"-",MONTH(A1),"-",DAY(A1)," my date")

Related
How to return only the Date from a SQL Server DateTime datatype
How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
How to subtract a day from a date?
How do I get the current date in JavaScript?
How to format a JavaScript date
Java string to date conversion
How to get current time and date in Android
SSRS date format in Excel with localization
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops