Counting dates

[k209310]

Is there any way of quickly searching a list of 600 orders organised in to dates(dd/mm/yy) and returning the number of orders that exist per month and year (mm/yy).

 

[Ian Mac]

Here's a nicer way, where you won't need a formula.

Go with Pivot Table idea,

Steps for you table;

When going through the wizard
Put the data field in the Row part of the table and the any data you need in the Data part.

Finish the wizard and you soulh have something like:

Book3
	A	B	C	D	E
1	Date	Data		SumofData2
2	01-Jan-01	416		Date	Total
3	08-Jan-01	328		01-Jan-01	416
4	15-Jan-01	386		08-Jan-01	328
5	22-Jan-01	401		15-Jan-01	386
6	29-Jan-01	440		22-Jan-01	401
7	05-Feb-01	360		29-Jan-01	440
8	12-Feb-01	346		05-Feb-01	360
9	19-Feb-01	437		12-Feb-01	346
10	26-Feb-01	413		19-Feb-01	437
11	05-Mar-01	429		26-Feb-01	413
12	12-Mar-01	334		05-Mar-01	429
13	19-Mar-01	413		12-Mar-01	334
14	26-Mar-01	420		19-Mar-01	413
15	02-Apr-01	491		26-Mar-01	420
16	09-Apr-01	329		02-Apr-01	491
17	16-Apr-01	424		09-Apr-01	329
18	23-Apr-01	490		16-Apr-01	424
19	30-Apr-01	457		23-Apr-01	490
20	07-May-01	447		30-Apr-01	457
21	14-May-01	477		07-May-01	447
22				14-May-01	477
23				GrandTotal	8238
Sheet2

Then Right-Click on the date part (any number in the column) Choose:

Group and Outline>Group
Select Months and OK.

You should now have:

Book3
	D	E	F	G
1	SumofData2
2	Date	Total
3	Jan	1971
4	Feb	1556
5	Mar	1596
6	Apr	2191
7	May	924
8	GrandTotal	8238
Sheet2

Also, if you need the count of the no items compareed to the date, Right click on your data, choose Field and change from sum to count.

Hope you got all that.

P.S. is it the Real Newcastle in Geordieland?

Edit: Can't spool Korectlee
_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-09-26 04:14