how do I sort column by date while ignoring year?

[monere]

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

 

[footoo]

One way is with a helper column.
Put the following formula in a blank column and sort by that column :
=TEXT(A1, "mmdd")

 

[monere]

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

 

[footoo]

I'll be cheeky and ask whether there's a more elegant way of doing it, like clicking a button.

I think only with VBA.

 

[monere]

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]

The coding is quite simple. For example :

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]

The coding is quite simple. For example :

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]

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
	F	G	H	I	J	K	L
1	Data	Expiry Date	Copy Date		Data	Expiry Date	Copy Date
2	Data 1	Mar-25	25/03/2021		Data 11	Jan-06	6/01/2023
3	Data 2	Sep-11	11/09/2021		Data 14	Jan-21	21/01/2021
4	Data 3	Apr-18	18/04/2021		Data 1	Mar-25	25/03/2021
5	Data 4	Dec-21	21/12/2020		Data 3	Apr-18	18/04/2021
6	Data 5	Jul-24	24/07/2020		Data 10	Apr-19	19/04/2022
7	Data 6	Jul-12	12/07/2022		Data 8	Apr-28	28/04/2022
8	Data 7	Dec-11	11/12/2020		Data 13	Jun-29	29/06/2020
9	Data 8	Apr-28	28/04/2022		Data 6	Jul-12	12/07/2022
10	Data 9	Nov-12	12/11/2020		Data 12	Jul-22	22/07/2022
11	Data 10	Apr-19	19/04/2022		Data 5	Jul-24	24/07/2020
12	Data 11	Jan-06	6/01/2023		Data 2	Sep-11	11/09/2021
13	Data 12	Jul-22	22/07/2022		Data 9	Nov-12	12/11/2020
14	Data 13	Jun-29	29/06/2020		Data 7	Dec-11	11/12/2020
15	Data 14	Jan-21	21/01/2021		Data 4	Dec-21	21/12/2020
Sort

Cell Formulas
Range	Formula
J2:L15	J2	=SORTBY(F2:H15,TEXT(G2:G15,"mmdd"))
H2:H15	H2	=G2
Dynamic array formulas.

 

[monere]

details updated. And thanks for the visual example, I appreciate it

 

[Peter_SSs]

details updated.

Great thanks.

Unfortunately it means that my formula suggestion is not available to you.