Sorting Excel Dates in Chronological Order

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Perhaps with a helper column (that can be hidden)starting in B2 and pulled down
Excel Formula:
=DATE(LEFT(A2,4),MONTH(1&LEFT(RIGHT(A2,LEN(A2)-5),3)),1)
and sort col A by this helper column
 
Upvote 0
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
ABCDE
1MONTHAMOUNTMONTHAMOUNT
22018 APRIL692018 JANUARY60
32018 APRIL302018 JANUARY50
42018 APRIL692018 JANUARY31
52018 APRIL262018 JANUARY48
62018 AUGUST112018 FEBRUARY11
72018 AUGUST292018 FEBRUARY70
82018 AUGUST442018 FEBRUARY17
92018 AUGUST672018 FEBRUARY55
102018 DECEMBER672019 FEBRUARY40
112018 DECEMBER592019 FEBRUARY16
122018 DECEMBER762019 FEBRUARY2
132018 DECEMBER312019 FEBRUARY28
142018 FEBRUARY112018 MARCH68
152018 FEBRUARY702018 MARCH60
162018 FEBRUARY172018 MARCH59
172018 FEBRUARY552018 MARCH23
182018 JANUARY602018 APRIL69
192018 JANUARY502018 APRIL30
202018 JANUARY312018 APRIL69
212018 JANUARY482018 APRIL26
222018 JULY22019 APRIL83
232018 JULY82019 APRIL80
242018 JULY802019 APRIL38
252018 JULY142019 APRIL59
262018 JUNE532018 MAY16
272018 JUNE142018 MAY85
282018 JUNE612018 MAY37
292018 JUNE112018 MAY41
302018 MARCH682018 JUNE53
312018 MARCH602018 JUNE14
322018 MARCH592018 JUNE61
332018 MARCH232018 JUNE11
342018 MAY162018 JULY2
352018 MAY852018 JULY8
362018 MAY372018 JULY80
372018 MAY412018 JULY14
382018 NOVEMBER942018 AUGUST11
392018 NOVEMBER942018 AUGUST29
402018 NOVEMBER592018 AUGUST44
412018 NOVEMBER462018 AUGUST67
422018 OCTOBER652019 AUGUST9
432018 OCTOBER362019 AUGUST41
442018 OCTOBER262019 AUGUST72
452018 OCTOBER852019 AUGUST15
462018 SEPTEMBER872018 SEPTEMBER87
472018 SEPTEMBER392018 SEPTEMBER39
482018 SEPTEMBER222018 SEPTEMBER22
492018 SEPTEMBER312018 SEPTEMBER31
502019 APRIL832018 OCTOBER65
512019 APRIL802018 OCTOBER36
522019 APRIL382018 OCTOBER26
532019 APRIL592018 OCTOBER85
542019 AUGUST92018 NOVEMBER94
552019 AUGUST412018 NOVEMBER94
562019 AUGUST722018 NOVEMBER59
572019 AUGUST152018 NOVEMBER46
582019 DECEMBER552018 DECEMBER67
592019 DECEMBER552018 DECEMBER59
602019 DECEMBER272018 DECEMBER76
612019 FEBRUARY402018 DECEMBER31
622019 FEBRUARY162019 DECEMBER55
632019 FEBRUARY22019 DECEMBER55
642019 FEBRUARY282019 DECEMBER27
Sort Dates
Cell Formulas
RangeFormula
D2:E64D2=SORTBY(A2:B64,DATEVALUE(MID(A2:A64,6,9)&LEFT(A2:A64)))
Dynamic array formulas.
 
Upvote 0
An alternative solution with Power Query

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"
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top