how do I sort column by date while ignoring year?

monere

Board Regular
Joined
Jul 12, 2014
Messages
139
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have an excel file with domain names, and one of the columns displays their expiry dates in this format: MMM-DD

Now, I want to be able to sort this list by month and day (in THIS order) - so, completely ignoring the year - so I can easily see which domains are approaching renewal date so I can, obviously, renew them. But the problem is that I don't know how to do this godlike-type of sorting, which is why I'm asking you jedi masters of excel :)

So, can someone help me solve my little sorting issue? Pretty please? :)

John
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,219
Office Version
  1. 2016
Platform
  1. Windows
One way is with a helper column.
Put the following formula in a blank column and sort by that column :
=TEXT(A1, "mmdd")
 
Solution

monere

Board Regular
Joined
Jul 12, 2014
Messages
139
Office Version
  1. 2016
Platform
  1. Windows
One way is with a helper column.
Put the following formula in a blank column and sort by that column :
=TEXT(A1, "mmdd")

I'll be cheeky and ask whether there's a more elegant way of doing it, like clicking a button. I mean, I do own excel 2016 and I'd expect the guys at Microsoft to have made this a one click thing by 2016 :)

If that's not possible your formula worked just fine so I'll stick with it for now. Thanks :)
 

monere

Board Regular
Joined
Jul 12, 2014
Messages
139
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I think only with VBA.

VBA is like coding / programming stuff, so that's even more complicating. No importance, though, I'll stick with the formula you gave me :)

Cheers, amigo!
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,219
Office Version
  1. 2016
Platform
  1. Windows
The coding is quite simple. For example :
VBA Code:
Sub v()
[A:A].Insert
With Range("A1:A" & Cells(Rows.Count, 2).End(3).Row)
    .Formula = "=TEXT(B1,""mmdd"")"
    .EntireRow.Sort Key1:=[A1], Order1:=xlAscending, Header:=xlYes
End With
[A:A].Delete
End Sub
 

monere

Board Regular
Joined
Jul 12, 2014
Messages
139
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

The coding is quite simple. For example :
VBA Code:
Sub v()
[A:A].Insert
With Range("A1:A" & Cells(Rows.Count, 2).End(3).Row)
    .Formula = "=TEXT(B1,""mmdd"")"
    .EntireRow.Sort Key1:=[A1], Order1:=xlAscending, Header:=xlYes
End With
[A:A].Delete
End Sub

for me this is alien stuff :D

I'm a simple man, doing and enjoying simple things in life. Still, thanks for taking the time to post this and please don't remove it cause I might give it a try when I have some time to delve into excel's capabilites more
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, this does not sort the original data but will sort a copy of the data with a single formula in a single cell IF you have excel 365.

For my sample data below I have made a copy of the expiry date in Column H. This is not required to get the solution but included so that readers here can easily see what the actual data in column G is.
The single formula in cell J2 automatically 'spills' the results across and down as required

21 01 08.xlsm
FGHIJKL
1DataExpiry DateCopy DateDataExpiry DateCopy Date
2Data 1Mar-2525/03/2021Data 11Jan-066/01/2023
3Data 2Sep-1111/09/2021Data 14Jan-2121/01/2021
4Data 3Apr-1818/04/2021Data 1Mar-2525/03/2021
5Data 4Dec-2121/12/2020Data 3Apr-1818/04/2021
6Data 5Jul-2424/07/2020Data 10Apr-1919/04/2022
7Data 6Jul-1212/07/2022Data 8Apr-2828/04/2022
8Data 7Dec-1111/12/2020Data 13Jun-2929/06/2020
9Data 8Apr-2828/04/2022Data 6Jul-1212/07/2022
10Data 9Nov-1212/11/2020Data 12Jul-2222/07/2022
11Data 10Apr-1919/04/2022Data 5Jul-2424/07/2020
12Data 11Jan-066/01/2023Data 2Sep-1111/09/2021
13Data 12Jul-2222/07/2022Data 9Nov-1212/11/2020
14Data 13Jun-2929/06/2020Data 7Dec-1111/12/2020
15Data 14Jan-2121/01/2021Data 4Dec-2121/12/2020
Sort
Cell Formulas
RangeFormula
J2:L15J2=SORTBY(F2:H15,TEXT(G2:G15,"mmdd"))
H2:H15H2=G2
Dynamic array formulas.
 

monere

Board Regular
Joined
Jul 12, 2014
Messages
139
Office Version
  1. 2016
Platform
  1. Windows
details updated. And thanks for the visual example, I appreciate it :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,499
Messages
5,636,682
Members
416,935
Latest member
Atulcp

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
Top