Sorting Dates with VBA

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I am trying to sort a column of dates using VBA. The dates are in the
format of dd-mmm.

The problem is that 12-jan (2004) is being sorted before 24-nov (2003).
Does anybody know how to fix this????

Here are the dates. (all of the dates contain the year in the cell),
but the format of the cell is "dd-mmm" .

12-Jan
24-Nov
20-Jan
20-Jan
21-Jan
16-Feb
23-Feb
26-Feb
9-Mar
22-Mar
13-Apr
19-Apr
19-Apr
10-May
24-May
24-May


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Formatting the cell to hide the year doesn't change the value of the date. The
years are still used.

If you want to sort the data so that the year is ignored, you could use a column
of helper cells:

=date(2004,month(a1),day(a1))

and sort your range (both columns???) by that helper column.

If you're trying to sort by the day, then month, use this as your helper
formula:

=text(a1,"ddmm")

October 27, 2003 will look like: 2710, but you'll have all the days grouped
together.

(I'm guessing that you really want to use the first version--sort a list of
people by birthdays/anniversary dates, etc--so all the Junes are together.)
 
I misread your message. Please ignore this.

Dave said:
Formatting the cell to hide the year doesn't change the value of the date. The
years are still used.

If you want to sort the data so that the year is ignored, you could use a column
of helper cells:

=date(2004,month(a1),day(a1))

and sort your range (both columns???) by that helper column.

If you're trying to sort by the day, then month, use this as your helper
formula:

=text(a1,"ddmm")

October 27, 2003 will look like: 2710, but you'll have all the days grouped
together.

(I'm guessing that you really want to use the first version--sort a list of
people by birthdays/anniversary dates, etc--so all the Junes are together.)
Click to expand...
 
Back
Top