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

questforexcel

Board Regular
Joined
Jan 18, 2019
Messages
92
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,337
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jan 18, 2019
Messages
92
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top