Unable to sort dates from oldest to newest

[blackystrat]

Hi


So here is the situation. I have an excel 2007 spreadsheet that has a column with dates that I need to sort from oldest to newest or vice versa.


Here's what I did so far:


1. Did Text-to-Column > Finish
2. Even after step #2, I was still unable to sort them from oldest to newest.
3. So I went to Text-to-Column > Next > Selected Date (DMY) > Finish
4. The column still wouldn't sort date wise. So I repeated the above process #4 and this time, I changed the date format to MDY and back again to DMY
5. This time I was getting the sort from Oldest to Newest option but all the dates didn't switch back to the same format. Some remained in mm/dd/yyyy while some changed to dd/mm/yyyy


The weird thing is that, if after I perform step #2 and copy the column to a new sheet, I would immediately get the sort datewise option right away, but it isn't happening on the existing sheet.

Sample sheet attached at - datessample.xlsx - Send Files Online - TempSend.com


Kindly advise where I am going wrong


Hope I was able to explain my situation properly

 

[FDibbins]

I am not able to access file-hosting sites, so cannot see your file. But your dates are probably not real dates, but text that looks like a date.

Test with =ISNUMBER(cell-ref)...FALSE indicated text dates, and we will need to convert them

 

[jtakw]

Hi,

All your dates in Column A are text, so you are correct to do "Text to Columns"; however, all your dates are in the format of MDY, so in your OP Step 4, don't change it back to DMY, and it should work.

 

[blackystrat]

Hi,

All your dates in Column A are text, so you are correct to do "Text to Columns"; however, all your dates are in the format of MDY, so in your OP Step 4, don't change it back to DMY, and it should work.

Many thanks for your response.

After I did text to column with date format selected as MDY, I wasn't getting the sort Oldest to Newest option, but when I switched it to DMY, that option was enabled.

Any idea why that was happening?

Thanks

 

[blackystrat]

I am not able to access file-hosting sites, so cannot see your file. But your dates are probably not real dates, but text that looks like a date.

Test with =ISNUMBER(cell-ref)...FALSE indicated text dates, and we will need to convert them

Thanks Ford. I will check this and revert

 

[jtakw]

Many thanks for your response.

After I did text to column with date format selected as MDY, I wasn't getting the sort Oldest to Newest option, but when I switched it to DMY, that option was enabled.

Any idea why that was happening?

Thanks

Can't answer your question because I used your uploaded file to test, and it's all working using MDY...

 

[blackystrat]

I am not able to access file-hosting sites, so cannot see your file. But your dates are probably not real dates, but text that looks like a date.

Test with =ISNUMBER(cell-ref)...FALSE indicated text dates, and we will need to convert them

Hi Ford

I ran the formula and it returned as False

Please advise.

Thanks

 

[jtakw]

Hi Ford

I ran the formula and it returned as False

Please advise.

Thanks

That =ISNUMBER(Cell reference) Test is to check if the cell contains Text or Number, and I already told you your dates are Text in post #3...

The quickest and easiest way to convert the Text dates to Real dates is by "Text to Columns", but apparently, that's failing you...

So:

Excel 2010
	A	B
1	Admitted
2	04/10/1979	4/10/1979
3	10/18/1973	10/18/1973
4	10/22/1990	10/22/1990
5	04/25/1990	4/25/1990
6	06/20/1969	6/20/1969
7	12/20/1974	12/20/1974
8	06/10/1968	6/10/1968
9	10/18/1973	10/18/1973
10	11/19/1976	11/19/1976
11	04/10/1978	4/10/1978
12	11/12/1987	11/12/1987
13	10/27/1975	10/27/1975
14	12/02/1968	12/2/1968
15	09/24/1997	9/24/1997
16	09/20/1990	9/20/1990
17	05/22/1975	5/22/1975
18	04/26/1991	4/26/1991
19	01/05/1979	1/5/1979
20	04/17/2001	4/17/2001
21	10/29/1982	10/29/1982
22	05/31/1977	5/31/1977
23	11/18/1983	11/18/1983
24	06/23/2005	6/23/2005
25	10/05/1987	10/5/1987
26	11/02/1984	11/2/1984
27	04/27/1994	4/27/1994
28	09/16/1988	9/16/1988
29	05/22/1975	5/22/1975
30	10/10/2003	10/10/2003
31	06/20/1985	6/20/1985
32	11/19/1976	11/19/1976
33	11/16/1978	11/16/1978
34	11/19/1976	11/19/1976
35	10/06/1972	10/6/1972
36	09/22/1989	9/22/1989
37	04/15/2002	4/15/2002
38	02/04/1991	2/4/1991
39	07/05/1979	7/5/1979
40	12/05/1978	12/5/1978
41	06/03/1983	6/3/1983
42	09/18/1978	9/18/1978
43	11/01/1979	11/1/1979
44	06/19/1989	6/19/1989
45	10/07/1994	10/7/1994
46	10/31/1980	10/31/1980
47	10/16/1986	10/16/1986
48	12/31/1991	12/31/1991
49	09/16/1983	9/16/1983
50	10/18/1984	10/18/1984
Sheet1

Cell Formulas
Range	Formula
B2	=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))

Formula copied down.

 

[FDibbins]

Hi Ford

I ran the formula and it returned as False

Please advise.

Thanks

What does your data look like, and where does it come from?

 

[blackystrat]

What does your data look like, and where does it come from?

The data is a list of professionals obtained from the public directory of an organization. It has multiple columns like name, address etc and the date column is of their admission dates to that specific organization.

When I run text-to-column > next > date, the default date format appears as DMY (even though the dates are actually in MDY)

When I click finish and try to sort again, it doesn't give me the option to sort date wise.

If I change the format to MDY, then the format reverses and it makes it dd/mm/yyyy. Please check the screenshot below showing the column before (red) and after (green) the text-to-column > date (MDY) sort.

So things are actually working in reverse for me here.

I ran the formula

[COLOR=#574123]=DATE([/COLOR][COLOR=Blue]RIGHT([COLOR=Red]A2,4[/COLOR]),LEFT([COLOR=Red]A2,2[/COLOR]),MID([COLOR=Red]A2,4,2[/COLOR])[/COLOR][COLOR=#574123])[/COLOR]

but it didn't return proper results

So either something is wrong with the way my excel is working or what I am doing to sort these out. I will try this using Office 2010 on my laptop and let you guys know

Thank you very much for your help so far