Date conversion

[GreekGuy9999]

PLEASE any help would be great.

So in my sheet, I have a column of dates in the format yyyymmdd. I needed to convert them to mm/dd/yyyy

I inserted a column next to the yyyymmdd column and used the formula =DATE(LEFT(E6,4),MID(E6,5,2),RIGHT(E6,2))

It wored! It gave me what I need which is mm/dd/yyyy. But now I want to delete the column with yyyymmdd only to have the NEW column of mm/dd/yyyy.

When I delete the column, it whacks out all the properly converted dates most probably because the formula was referencing the deleted column.

How can I keep the new dates while deleting the old dates column?

I am NOT an expert in Excel by any means. so PLEASE be gentle with an explanation. I do very appreciate any help anyone can give me.

 

[hagia_sofia]

Hello, maybe you could just select the new column copy it and paste it the same column as values?

 

[Alex Blakenburg]

It sounds like you don't want to use a formula but just do a manual conversion.
Try this.
• Select the column
• On the Ribbon select Data > Text to Columns (just past where you see Help)
• Select Delimited
• Hit Next twice
• You should see the screen below.
Click date and from the drop down select YMD
• Hit Finish

Let us know how you go.

 

[GreekGuy9999]

It sounds like you don't want to use a formula but just do a manual conversion.
Try this.
• Select the column
• On the Ribbon select Data > Text to Columns (just past where you see Help)
• Select Delimited
• Hit Next twice
• You should see the screen below.
Click date and from the drop down select YMD
• Hit Finish

Let us know how you go.

View attachment 115911

OMG.. it was THAT EASY??? Jeez Louise. I cannot tell you how much I appreciate the answer. I was searching for hours and had tried your way except I saw the selection YMD in the Text To Column pulldown, but I needed MM/DD/YYYY and didn't think YMD would yield that (because it didn't match the format I can create in the FORMAT CELLS / CUSTOM menu.

Again, a thousand thank yous!

 

[MARK858]

and didn't think YMD would yield that (because it didn't match the format I can create in the FORMAT CELLS / CUSTOM menu.

In Text to Columns when converting dates you always select the option with the format it is converting from i.e. the date format you currently see, hence the YMD