Is there a way to sort dates on month and day and ignore year?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,691
Office Version
  1. 365
Platform
  1. Windows
Is there way to sort a column of dates on just the month and day, ignoring the year? This is the only way I could think of by adding a helper column.

Family Database.xlsx
BCD
3DoBAgemm/dd
42/03/1944 79.00203
52/14/1999 24.00214
62/25/1963 60.00225
74/06/1968 54.90406
86/19/1997 25.70619
97/06/1949 73.60706
108/30/1968 54.50830
1111/13/1951 71.31113
1211/15/2005 17.21115
1312/08/2007 15.21208
1412/13/1945 77.21213
1512/19/1966 56.21219
MrExcel-02
Cell Formulas
RangeFormula
C4:C15C4=IF( IsADate([@DoB]),CONVERT(TODAY()-[@DoB],"day","yr"),"")
D4:D15D4=TEXT([@DoB],"mm") & TEXT([@DoB],"dd")
Named Ranges
NameRefers ToCells
'MrExcel-02'!DoBBurke='MrExcel-02'!$B$14C14:D14
'MrExcel-02'!DoBWes='MrExcel-02'!$B$4C4:D4
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I think a helper column should be the best way to do what you want; also, you can shorten the formula to:
Excel Formula:
=TEXT([@DoB],"mmdd")
 
Upvote 0
Solution
You could use the SORTBY function but that will mean having a seperate sorted range:
Excel Formula:
=SORTBY(A2:C13,MONTH(A2:A13),1,DAY(A2:A13),1)
 
Upvote 0
Beside VBA, I think helper columns is the only solution to sort.
Alternative formula in D4:
=MONTH(B4)*100+DAY(B4)
With VBA, no helper column needed.
 
Upvote 0
Beside VBA, I think helper columns is the only solution to sort.
Alternative formula in D4:
=MONTH(B4)*100+DAY(B4)
With VBA, no helper column needed.
I like that formula. It has the advantage ofr returning a number, not text.

Since I have a simple solution, I probably wouldn't resort to VBA. But just out of curiosaity, how would a VBA solution work?

Thanks
 
Upvote 0
But just out of curiosaity, how would a VBA solution work?
You wouldn't need column D, and on a click of a button (or whichever way the macro is triggered to run), columns B and C (including any other defined range/column) can be automatically sorted according to your criteria.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

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