Date conversion

[BarnTalker]

I downloaded some data into a spreadsheet from a website that I want sorted by date.

The dates came in as "Mar-14-12" with the cells formatted as "General". I also got those little green triangle in the left hand corner which then tells me "This cell contains a date string represented with only 2 digits for the year." when I hover over the question mark.

I want the dates to appear as "3/14/12".

I highlighted the cells and changed the Category from "General" to "Date" and set "Type" as 3/14/12. Now, when I click on a cell, the category has changed to "Date" and the "Type" has changed to "3/14/12", but it still displays as "Mar-14-12". The little green triangles are still there, and I still get the message about only 2 digits for the year. No matter what I do, I cannot get the date to display as I want it to.

I thought OK, the format doesn't really matter as long as the data sorts properly on the date. So I tried sorting. Unfortunately, it sorted by the Month then the day but not the year. It sorted as:
Dec-07-16, Dec-09-15, Dec-10-17, Dec-28-15, Dec-28-17, Feb-10-16, Feb-14-17, etc.

I tried the =DATEVALUE function, but I can't get that to work either.

I must be missing something

What can I do short of manually re-entering all of the dates?

 

[Joe4]

Welcome to the Board!

The dates came in as "Mar-14-12" with the cells formatted as "General"

This means that your data is actually stored as Text. Formatting only applies to numbers (Dates are numbers, specifically the number of days since 1/0/1900), and the General format will never return something like "Mar-14-12".

You need to convert these to valid dates. You can do that by selecting your column, selecting "Text to Columns" from the Data menu, go to the 3rd step and choose the Date format, with the "MDY" option, and clicking finish. Then these will all be converted to dates, and you can sort and apply any date format you wish.

 

[BarnTalker]

I think I messed up my original response so this may be a duplicate:

I thank you for your welcome!

I did as you suggested and the one column was split into 3 columns. (I had to insert 2 columns first, otherwise the columns to the right was overwritten.) I was then able to sort by the year column followed by month then year. So the data is now in the order I wanted. But is there a way to re-combine the 3 columns back into 1 formatted as a date?

 

[Jonmo1]

I think you probably had a hyphen as the delimiter on the 2nd step of the Text To Columns procedure.

If you can revert back to your data's original state, The Text To Columns procudure should be like this

Select the column
Data - Text To Columns
Select Deliminated - Click Next
UNcheck ALL options - Click Next
Choose Date - MDY
Click Finish.

 

[BarnTalker]

Thank you, thank you, thank you! That worked perfectly.
MrExcel.com is now my go-to site for Excel help.
I've found Microsoft's help to be pretty useless, and they've even been insultingly snarky with me.
I'm so glad I found this site!

 

[Joe4]

Ah, yes. I should have clarified that you do not want to delimit your data. Thanks for the assist, Jonmo.

 

[Jonmo1]

You're welcome.