challenge 2

[is1ma1il]

I have columns in an excel spreadsheet with thousands of rows of data that is based on date e.g. 01-JUN-03 or 10-JUN-03.
I need to count how many rows are present per month.

tried dcount but not sure what am doing.

 

[Zathras]

Use a pivot table:

Put the date column as the row and count some other column as the data.

Then when the table is generated, right-click, select "Group and Outline / Group..." and then choose "Months".

 

[SkipVought]

Hi,

This can be done quite simply with a Pivot Table. In the Pivot Table Wizard setup, you will drag the date into either the ROW or COLUMN area and in the DATA area.

In the DATA area, specify that you want a Count.

Finish the Pivot Table Wizard, and right click on the Date heading in the Pivot Table, selecting Group. Select Months and ...

VOLA!

It took me less than 30 seconds, creating some test data and creating the Pivot table that counts by months.

If you need to discriminate my Year as well (ie June 2003 does NOT count with June 2002) then ALSO select Year in the Group Window.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[is1ma1il]

it says that i cannot group by the date selection

 

[Zathras]

Are your "dates" actually dates or just text that looks like dates?

Both Skip and I were able to make it work just the way you want it to. Tell us more about your data.

Also, what versions of Excel and Windows are you working with?

 

[is1ma1il]

sorry it worked when the data was cleaned, it had blanks in there.

Thanks guys