Sorting Dates

G

Guest

Guest
Sorry, this has probably been beaten to death...

I have a column set up as MM/DD/YY, and I need to be able to sort on the MM. All I can get it to do is sort on the YY.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
In the next available column, write =month(a1), change the range to the approprite column with MM/DD/YY, and the same row. Copy the formula down, sort on the results and delete the contents of your new column.

Cheers,

Nate
This message was edited by NateO on 2002-02-28 13:56
 
Upvote 0
ok, i figured it out...

I did =month(C:C) in one column, and =day(B:B) in another column, and just sorted on those two columns. tx.
 
Upvote 0
It's a bit clunky but it works. Excel stores dates as numbers; I cannot think of a way to sort on every 28/30/31's digit depending on day, so I have opted to split the date up and attack it from there.
I've done my work with the dates in coulmn E. Adjust to suit.


CODE:


Sub sortbymonth()

Range("e1").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
noofrows = Selection.Rows.Count
'insert the colums we need
Columns("F:H").Select
Selection.Insert Shift:=xlToRight
'select the column with the dates in; this one column E
Columns("E:E").Select
'use the text to columns to split the data in columns, column 1 months, 2 day, 3 year
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
'format the first row in number format (it is still hung up in the date format)
Columns("e:H").Select
Selection.NumberFormat = "0"
'selct the columns to sort
Columns("E:I").Select
'sort by column e, the month column, other data in column i. expand to contain...
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For i = 0 To (noofrows - 1)
Monthval = Range("e1").Offset(i, 0).Value
Dayval = Range("f1").Offset(i, 0).Value
yearval = Range("g1").Offset(i, 0).Value
Dateval = Monthval & "/" & Dayval & "/" & yearval

Range("H1").Offset(i, 0).Formula = Dateval


Next i

'formats the cells properly
Columns("H:H").Select
Selection.NumberFormat = "m/d/yyyy"
'delets the working columns
Columns("E:G").Select
Range("G1").Activate
Selection.Delete Shift:=xlToLeft
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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