showing month in pivot tables


Posted by Paul on July 19, 2001 7:13 PM

I have a worksheet that I use to keep up with the workorders at my work, the columns are "A" event number "B" date received "C" date completed "D" building name and "E" unit number I want to use a pivot table to track the number of work orders each unit gets in each month, when I put the date received and unit number in the pivot table it shows me each day of the month. How do I get it to show the workorders buy month only? I have tried making a new column "F" and putting in it =B1(date received) and copying it down and formatting it as MMMM this gives me the months January - December but the pivot table nonetheless shows it as January - December but as each day. I can type the months in column "F" that corresponds to the date received and the table will show the right numbers. How do I get the table to show the workorders by months based on what is in column "B"? Thanks in advance for your help.

Posted by Aladin Akyurek on July 20, 2001 1:58 AM

===============

In F1 enter: =VLOOKUP(MONTH(B1),{1,"January";2,"February";3,"March";4,"April";5,"May";6,"June";7,"July";8,"August";9,"September";10,"October";11,"November";12,"December"},2,0)

Copy down this formula as far as needed. And Retry pivoting. Note. I just picked up your own suggestion. I didn't investgiate whether it can be done directly in PivotTables without an additional column.

Aladin



Posted by Mark W. on July 20, 2001 5:58 AM

Group your dates!