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
95
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,893
Office Version
  1. 2019
  2. 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
95
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,281
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top