Sort date column by year Excel vba

[Rowland Hamilton]

Folks: How can I sort a date column by year without creating a parallel, offset column that holds the formula like Year(rcell.value)? like:

'Sort
    'change sort keys to year(column 3.value), column 1, column 3
    sheet1.UsedRange.Offset(6, 0).Sort _
    key1:=Sheet1.UsedRange.>>Year of these values<<.Columns(1).Offset(7, 0).Resize(1, 1), _
    order1:=xlAscending, _
    key2:=Sheet1.UsedRange.Columns(1).Offset(7, 0).Resize(1, 1), _
    order1:=xlAscending, _
    key3:=Sheet1.UsedRange.Columns(3).Offset(7, 0).Resize(1, 1), _
    order1:=xlAscending, _
    Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

My guess is impossible, but...
Thank you - Rowland

 

[Joe4]

Is the value a real date?
If so just sort by that. Anything sorted by data will be sorted by year inherently (as year is part of date). It is impossible to sort by date and not have the years come out right.

 

[Rowland Hamilton]

That's what I realized when I sorted it, since Excel uses the date serial so it recognizes proper date order.

However, I think my End User may actually wants sort by year only descending, then by product name ascending, then by full date descending. That, I haven't figured out yet, but doesn't seem possible without using an additional column for year only like formula = year(date).

Thank you, Rowland

 

[Joe4]

However, I think my End User may actually wants sort by year only descending, then by product name ascending, then by full date descending. That, I haven't figured out yet, but doesn't seem possible without using an additional column for year only like formula = year(date).

I believe you are correct. It needs a column to sort by. I don' think it is possible without one. However, if you do this with a macro, you can have the macro insert a temporary column that gets the year, sorts the records like you want, then delete the temporary column.