Formula applying apostrophe + date to column

[Excellica]

Hello!

I have a kolumn in my spreadsheet that contains dates in this format: YYYY-MM-DD. The column is auto-formatted as "Date". However, I need to import this column into a CRM system that happens to re-write the dates into an unwanted date format. After some trying back and forth I managed to get it imported correctly by adding an apostrophe " ' " before the date in the file (auto-formatting it to "General"). But, when I try to add this apostrophe to all rows through a formula e.g.: ="'"&A2, Excel rewrites the date to a 5-digit number (formatting auto changes to "General"). ¯\_(ツ)_/¯

So my date that should look like this 'YYYY-MM-DD, ends up like this '12345.

What to do...?

 

[sandy666]

try
=TEXT(A2,"yyyy-mm-dd")
or
="'"&TEXT(A2,"yyyy-mm-dd")

but I think the first will be enough

 

[Excellica]

Date: 2020-02-12
My output when I try your suggestion: 'yyyy-00-12
So neither year nor month is correct

 

[sandy666]

what is you excel language?
maybe there should be :jjjj or rrrr
how is word "year" in your language?
month ?
day ?

 

[sandy666]

TEXT function - Microsoft Support

Syntax: TEXT(value, format_text)

support.office.com

check this function in your language

 

[Excellica]

Oooh it worked! It's in Swedish so it's åååå-mm-dd THANK YOU!

 

[sandy666]

glad you are HAPPY
thanks for the feedback

btw. you can set Location in your Account details :Sverige or Sweden, less troubles in the future

 

[Excellica]

Done

 

[sandy666]

Thanks, have a nice day