CODEWITHSUNDEEP
exceldateexcel-formuladate-formatting
user1981275
user1981275

Reputation: 13372

Excel convert dates from different formats

I have a data set with column A containing different date formats, the column format is date:

enter image description here

I would like to have all entries in the format dd-mm-yyyy, and one formula for the whole column.

With format cells, I could not get them into the same format. I tried TEXT(A3, "dd-mm-yyyy"), but this does not do anything. DATEVALUE(A3) gives me an error. Is there a simple solution to this?

Upvotes: 0

Views: 201

Answers (1)

zipa
zipa

Reputation: 27869

Given that Excel cannot parse the date before 01-01-1900 you'd need a proper date and then select your range, and go Data>Text to Columns.

Here, select Delimited and click Next. Deselect all options (usually only Tab is selected) and click Next. Select Date option and choose the format which seems to be DMY in your case.

Then you can use the formula such as:

=TEXT(A3,"dd-mm-yyyy")

Upvotes: 1

Related Questions