How to format date in Excel-2007 which appears like "20130530" ?

I wasn't sure if the cell contained

20130530

or if it actually contained te quote marks

"20130530"

but let's assume it's just 20130530

and let's say that is in A2

put this in B2

=DATE( MID(A2,1,4), MID(A2,5,2), MID(A2,7,2) )

that will give you a date in standard US format of mm/dd/yyyy

now right click on column header for the B column (so you're right clicking on the B)

that will select the entire B column

a menu should pop up

select "Format Cells"

a box pops up, select the tab named "Numbers"

choose "Date" from the list

now choose the format you want

if you don't see what you want then choose "Custom" instead of "Date"

it will give you various options

you can type in

dd-MMM-yyyy

to get the format you're looking for

breakdown of the function

look at the contents of A2, start at position 1 and get 4 characters (this is the year)

MID(A2,1,4)

look at the contents of A2, start at position 5 and get 2 characters (this is the month)

MID(A2,5,2)

look at the contents of A2, start at position 7 and get 2 characters (this is the day)

MID(A2,7,2)

then use DATE to put it all together

=DATE( MID(A2,1,4), MID(A2,5,2), MID(A2,7,2) )

you might have to adjust the starting positions

Make a macro to re-format in real date version as excel.

First download and install notepad++ from notepad-plus-plus.org/ it is lifetime freeware.

01.Copy the all "date column" and paste in notepad++

02.Keep cursor in the startup of first line.

03.Record a macro to do: (click Macro menu > Start Recording

Four time right arrow key. it will go between 2014 and 03.

Press Tab Key. Press Three Times right key, it will go between 03 and 05.

Press Tab Key. Press Down Key to go in second line and press Home key to go in the startup of line. It looks like this:

2014 03 05

20140305

20140305

20140305

20140305

20140305

20140305

Stop the recording. Macro (menu) > Stop Recording.

Run the macro to the end line by clicking. Macro (menu) > Run a macro multiple times ... > Run until the end of file.

Copy all data by Ctrl + A and Ctrl + C and paste in Excel. Now you can get seperate you date and month and years. Manage it as u want.

If you want to reformat as 30-05-2014 then use macro in notepad++.

Select the cell, right click and select Format cell from the menu. Choose the Numbers tab and pick the date format you would like.

This will work while selecting multiple cells or just a single cell.

Follow this process

Parse these numbers by putting a "slash" as 2013/05/2013.

Then click on HOME Tab - > on the Status Bar click on the a "Drop Down" displaying first item as GENERAL. --> Clicke DATE ...You Got it !! Congrats!