Sorting Excel Dates in Chronological Order

[Chris Waller]

Hi All,

I have an excel Spreadsheet that contains the year and the month in the same cell. What I would like to do is sort these into chronological order. Can anyone tell me if there is an easy way to do this, because what I have tried doesn't appear to have given me what I'm after? Thanks

MONTH

2018 APRIL

2018 APRIL

2018 APRIL

2018 APRIL

2018 AUGUST

2018 AUGUST

2018 AUGUST

2018 AUGUST

2018 DECEMBER

2018 DECEMBER

2018 DECEMBER

2018 DECEMBER

2018 FEBRUARY

2018 FEBRUARY

2018 FEBRUARY

2018 FEBRUARY

2018 JANUARY

2018 JANUARY

2018 JANUARY

2018 JANUARY

2018 JULY

2018 JULY

2018 JULY

2018 JULY

2018 JUNE

2018 JUNE

2018 JUNE

2018 JUNE

2018 MARCH

2018 MARCH

2018 MARCH

2018 MARCH

2018 MAY

2018 MAY

2018 MAY

2018 MAY

2018 NOVEMBER

2018 NOVEMBER

2018 NOVEMBER

2018 NOVEMBER

2018 OCTOBER

2018 OCTOBER

2018 OCTOBER

2018 OCTOBER

2018 SEPTEMBER

2018 SEPTEMBER

2018 SEPTEMBER

2018 SEPTEMBER

2019 APRIL

2019 APRIL

2019 APRIL

2019 APRIL

2019 AUGUST

2019 AUGUST

2019 AUGUST

2019 AUGUST

2019 DECEMBER

2019 DECEMBER

2019 DECEMBER

2019 FEBRUARY

2019 FEBRUARY

2019 FEBRUARY

2019 FEBRUARY

 

[arthurbr]

Perhaps with a helper column (that can be hidden)starting in B2 and pulled down

=DATE(LEFT(A2,4),MONTH(1&LEFT(RIGHT(A2,LEN(A2)-5),3)),1)

and sort col A by this helper column

 

[jeffreybrown]

How about a helper column with

=DATE(LEFT(A2,4),MONTH(MID(A2,FIND(" ",A2)+1,LEN(A2))&1),1)

 

[Chris Waller]

How about a helper column with

=DATE(LEFT(A2,4),MONTH(MID(A2,FIND(" ",A2)+1,LEN(A2))&1),1)

Thanks that was great.

 

[Chris Waller]

Thanks that was great.

Brilliant suggestion. Thanks

 

[Peter_SSs]

Just wondering if this might be any use. It leaves the original data as-is but reproduces it (& can include any other associated columns) in chronological order with a single formula cell.

22 09 28.xlsm
	A	B	C	D	E
1	MONTH	AMOUNT		MONTH	AMOUNT
2	2018 APRIL	69		2018 JANUARY	60
3	2018 APRIL	30		2018 JANUARY	50
4	2018 APRIL	69		2018 JANUARY	31
5	2018 APRIL	26		2018 JANUARY	48
6	2018 AUGUST	11		2018 FEBRUARY	11
7	2018 AUGUST	29		2018 FEBRUARY	70
8	2018 AUGUST	44		2018 FEBRUARY	17
9	2018 AUGUST	67		2018 FEBRUARY	55
10	2018 DECEMBER	67		2019 FEBRUARY	40
11	2018 DECEMBER	59		2019 FEBRUARY	16
12	2018 DECEMBER	76		2019 FEBRUARY	2
13	2018 DECEMBER	31		2019 FEBRUARY	28
14	2018 FEBRUARY	11		2018 MARCH	68
15	2018 FEBRUARY	70		2018 MARCH	60
16	2018 FEBRUARY	17		2018 MARCH	59
17	2018 FEBRUARY	55		2018 MARCH	23
18	2018 JANUARY	60		2018 APRIL	69
19	2018 JANUARY	50		2018 APRIL	30
20	2018 JANUARY	31		2018 APRIL	69
21	2018 JANUARY	48		2018 APRIL	26
22	2018 JULY	2		2019 APRIL	83
23	2018 JULY	8		2019 APRIL	80
24	2018 JULY	80		2019 APRIL	38
25	2018 JULY	14		2019 APRIL	59
26	2018 JUNE	53		2018 MAY	16
27	2018 JUNE	14		2018 MAY	85
28	2018 JUNE	61		2018 MAY	37
29	2018 JUNE	11		2018 MAY	41
30	2018 MARCH	68		2018 JUNE	53
31	2018 MARCH	60		2018 JUNE	14
32	2018 MARCH	59		2018 JUNE	61
33	2018 MARCH	23		2018 JUNE	11
34	2018 MAY	16		2018 JULY	2
35	2018 MAY	85		2018 JULY	8
36	2018 MAY	37		2018 JULY	80
37	2018 MAY	41		2018 JULY	14
38	2018 NOVEMBER	94		2018 AUGUST	11
39	2018 NOVEMBER	94		2018 AUGUST	29
40	2018 NOVEMBER	59		2018 AUGUST	44
41	2018 NOVEMBER	46		2018 AUGUST	67
42	2018 OCTOBER	65		2019 AUGUST	9
43	2018 OCTOBER	36		2019 AUGUST	41
44	2018 OCTOBER	26		2019 AUGUST	72
45	2018 OCTOBER	85		2019 AUGUST	15
46	2018 SEPTEMBER	87		2018 SEPTEMBER	87
47	2018 SEPTEMBER	39		2018 SEPTEMBER	39
48	2018 SEPTEMBER	22		2018 SEPTEMBER	22
49	2018 SEPTEMBER	31		2018 SEPTEMBER	31
50	2019 APRIL	83		2018 OCTOBER	65
51	2019 APRIL	80		2018 OCTOBER	36
52	2019 APRIL	38		2018 OCTOBER	26
53	2019 APRIL	59		2018 OCTOBER	85
54	2019 AUGUST	9		2018 NOVEMBER	94
55	2019 AUGUST	41		2018 NOVEMBER	94
56	2019 AUGUST	72		2018 NOVEMBER	59
57	2019 AUGUST	15		2018 NOVEMBER	46
58	2019 DECEMBER	55		2018 DECEMBER	67
59	2019 DECEMBER	55		2018 DECEMBER	59
60	2019 DECEMBER	27		2018 DECEMBER	76
61	2019 FEBRUARY	40		2018 DECEMBER	31
62	2019 FEBRUARY	16		2019 DECEMBER	55
63	2019 FEBRUARY	2		2019 DECEMBER	55
64	2019 FEBRUARY	28		2019 DECEMBER	27
Sort Dates

Cell Formulas
Range	Formula
D2:E64	D2	=SORTBY(A2:B64,DATEVALUE(MID(A2:A64,6,9)&LEFT(A2:A64)))
Dynamic array formulas.

 

[alansidman]

An alternative solution with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MONTH", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"MONTH", Order.Ascending}}),
    #"Inserted Month Name" = Table.AddColumn(#"Sorted Rows", "Month Name", each Date.MonthName([MONTH]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([MONTH]), Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Year", {{"Year", type text}}, "en-US"),{"Month Name", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"MONTH"})
in
    #"Removed Columns"