Group dates by month
 

Does anyone know how to group dates to by their months using
formulae/vba code. i do not want duplicate outputs for each row.

For example
01-Jan-03
05-Jan-03
14-Jan-03
01-Feb-03
01-Mar-03
01-Apr-03

output exactly:
jan-03
feb-03
mar-03
apr-03
etc

Thanks
Lai:D :D

Group dates by month
 

You can use the following formula to change all dates to first day in
the month

= date(year(A2),month(A2),1)

Then you can use advanced filter to create the output as you want (use
the check box to create unique records)

Sofia Vasconcellos


jigsaw2 wrote in message ...
Does anyone know how to group dates to by their months using
formulae/vba code. i do not want duplicate outputs for each row.

For example
01-Jan-03
05-Jan-03
14-Jan-03
01-Feb-03
01-Mar-03
01-Apr-03

output exactly:
jan-03
feb-03
mar-03
apr-03
etc

Thanks
Lai:D :D

Group dates by month
 

what i did before, in excel 97, is:
Step 1: highlight column A and then Format-Cells...- mmm-yy
date
Jan-03
Jan-03
Jan-03
Feb-03
Mar-03
Apr-03

Step 2: Data-Subtotal...
date
Jan-03
Jan-03
Jan-03
Jan-03 subtotal Jan-09
Feb-03
Feb-03 subtotal Feb-03
Mar-03
Mar-03 subtotal Mar-03
Apr-03
Apr-03 subtotal Apr-03

Step 3: click the "2" to show sub-level 2 only.
date
Jan-03 subtotal Jan-09
Feb-03 subtotal Feb-03
Mar-03 subtotal Mar-03
Apr-03 subtotal Apr-03

Step 4: highlight the four cells only
(Jan-03 subtotal,Feb-03 subtotal,Mar-03 subtotal,Apr-03 subtotal)

Step 5: Edit - Goto...- Special...-Visiable cells

Step 6: Copy

Step 7: Paste on some cell

Step 8: remove the words "subtotal" by Edit - Replace...

Here is the final result:
Jan-03
Feb-03
Mar-03
Apr-03

ok?

rgds.
alan
--END

jigsaw2 wrote in message ...
Does anyone know how to group dates to by their months using
formulae/vba code. i do not want duplicate outputs for each row.

For example
01-Jan-03
05-Jan-03
14-Jan-03
01-Feb-03
01-Mar-03
01-Apr-03

output exactly:
jan-03
feb-03
mar-03
apr-03
etc

Thanks
Lai:D :D