Formatting date cell

Question

I am trying to clean some data in excel but cannot make it use the Date as a string/text. Instead it inserts the date as numbers, see below. I have tried copying into notepad, pasting into excel as text. Make a cell with =1*B2, and then adding this. But same result!

Original:

Timestamp   Date    Data1 Data2
1539808680  2018-10-17 15:38    498 506

Formula:

=B2&";"&C2&";"&D2

Result:

Date;Data1;Data2
43390.6513888889;498;506

Answer 1

You need to wrap the date value in the text function:

=TEXT(B2;"YYYY-MM-DD")

So:

=TEXT(B2;"YYYY-MM-DD")&";"&C2&";"&D2

"YYYY-MM-DD" is a bit tricky... it's different depending on your regional settings. easiest way to find what you have is to right click on your cell date and choose "Format Cells":

Go down to Custom and the appropriate date combination. So in my excel version, which is nordic, I need to use: =TEXT(C3;"ÅÅÅÅ-MM-DD") to get it right.

Notice that I use ";" as delimiter. While US probably would use ",".