Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group by month in a Pivot table | Excel Discussion (Misc queries) | |||
PivotTable Group by Week and Month | Excel Discussion (Misc queries) | |||
Pivot table - group dates per week or month | Excel Discussion (Misc queries) | |||
PivotTables - Group Dates, excluding dates | Excel Discussion (Misc queries) | |||
Group by Month when no data | Excel Worksheet Functions |