Date format swapped i.e year figure read as day, and default value being applied for year

[questforexcel]

Hi,

I have tried searching online platforms and even used to text to columns and different formulas to sort the date format out, but it doesnt seem to work.

Below is the issue which I am facing:

My date column reads:

2019-09-18

While the actual date is 30-Sept-2018, excel had converted the 18 to day and took a default value of the year as 2019.

Could you please help me to convert all such values back to the Month and Year format.

I tried format cells and then selected date but even that didnt work.

Thank you

 

[Rick Rothstein]

My date column reads:

2019-09-18

While the actual date is 30-Sept-2018, excel had converted the 18 to day and took a default value of the year as 2019.

I am not following what you are saying...

What was the value in the cell before Excel "converted" it to the above?

What did you do that made Excel "convert" the date from whatever it was originally?

 

[questforexcel]

With "converted" I meant excel reads it as 2019 in place of 2018.

I took a dump of Sept-18 data and the date column was being read as "18-Sept" when I changed the format to DD-MM-YYYY format it read it as "18-Sept-2019" instead of "30-Sept-2018". I would like my data to be read either as "30-Sept-2018" or "Sept-2018"



I have tried Text to Columns and even date function using left mid and right for the specific cell but these don't seem to work.

I am not following what you are saying...

What was the value in the cell before Excel "converted" it to the above?

What did you do that made Excel "convert" the date from whatever it was originally?