Date Formatting Issue - Different formats recognised in a column

[malevasquez]

Hi everyone!
This is my first post here so I apologise in advance if there's anything wrong. I received an xlsx file which contains around 23000 rows and several columns, one of which is dates. For some reason, some cells aren't recognised with this format and, no matter what I try, they don't change. Below I attach some images for better explanation.

This is how it appears when I open the file. The shifted cells repeat themselves like a pattern throughout it all. When I try to change the format into anything (in this case year), some cells change and some don't.

I've searched for answers and tried everything (text to column, VALUE, DATEVALUE, formatting option) and more but no

 

[etaf]

it looks like some of those dates are actually Just text
Dates are numbers starting from 01/01/1900
If you change the format of the column to General or Number - then you will see some stay as dates and these are the text values
you will need to setup a formula to a new column
so in a new column put a formula
Assuming the dates start in A2
=IFERROR(DATEVALUE(A2),A2)

DATEVALUE should work OK
Now on the new column
copy the column
and paste>special>value into the old column

now you should be fine

Book1
	A	B	C	D
1	DATE -Some TEXT		CONVERSION	Date
2	1/1/20		43831	1/1/20
3	1/3/20		43833	1/3/20
4	1/2/20		43832	1/2/20
5	1/4/20		43834	1/4/20
6	1/3/20		43833	1/3/20
7	05/01/2020		43835	1/5/20
8	04/01/2020		43834	1/4/20
9	1/6/20		43836	1/6/20
10	1/5/20		43835	1/5/20
11	1/7/20		43837	1/7/20
12	06/01/2020		43836	1/6/20
13	08/01/2020		43838	1/8/20
14	07/01/2020		43837	1/7/20
15	1/9/20		43839	1/9/20
16	1/8/20		43838	1/8/20
17	1/10/20		43840	1/10/20
18	1/9/20		43839	1/9/20
19	1/11/20		43841	1/11/20
20	1/10/20		43840	1/10/20
21	1/12/20		43842	1/12/20
22	1/11/20		43841	1/11/20
Sheet1

Cell Formulas
Range	Formula
C2:C22	C2	=IFERROR(DATEVALUE(A2),A2)
D2:D22	D2	=C2

 

[Rick Rothstein]

Not so fast on that formula 'etaf'. If I am not mistaken, even the values that were changed to real dates were changed to the wrong real dates and need to be corrected as well. Note which ones remained unchanged... the day value was greater than 12 which indicates to me that for the ones that did change the day and month values got reversed in the conversion. For example, the first date shows as 1/02/1965 (which on the original system was January 2nd) but I'll bet on the OP's system it has become February 1st.

 

[etaf]

Thanks Rick Rothstein, as you say looks a US format v Europe format of dates , that maybe why datevalue() was not working correctly
@malevasquez -
What date format do you use on your system DD/MM/YYYY or MM/DD/YYYY, and where does the file come from, ? is it in excel format or csv, you say its send as xlsx
As Rick Rothstein
Says - the dates that are formatted correctly may actually be wrong because of the US v UK format

 

[malevasquez]

Thanks Rick Rothstein, as you say looks a US format v Europe format of dates , that maybe why datevalue() was not working correctly
@malevasquez -
What date format do you use on your system DD/MM/YYYY or MM/DD/YYYY, and where does the file come from, ? is it in excel format or csv, you say its send as xlsx
As Rick Rothstein
Says - the dates that are formatted correctly may actually be wrong because of the US v UK format

Thank you both for being so helpful. My system uses DD/MM/YYYY, which might be an issue considering the file uses MM/DD/YYYY. Originally the file is downloaded in .csv from a website (Significant Earthquakes, 1965-2016), but I receive the .xlsx. However, I can access either one if needed.