Counting dates

k209310

Active Member
Joined
Aug 12, 2002
Messages
382
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).
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

ThePencilQueen

Board Regular
Joined
Jun 26, 2002
Messages
109
Personally I would add in a couple of column pulling out the month and year and then do a pivot of the lot

There probably much quicker more elegant ways of doing this though...
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
then use GETPIVOTDATA function to get your answers.

Alternatively you could do a selection sort of the data and then a countif (in VB) but you'll probably find that The Pencil Queen's solution more straightforward.
 

k209310

Active Member
Joined
Aug 12, 2002
Messages
382

ADVERTISEMENT

This may be a stupid question but how do you get excel to count the number cells containing a specified date range eg 07/2001

If there are 6 cells in a list containing a specified date how can you get excel to return a value of 6.

I have been trying to use the ROWS function to carry this out?

I am a relative beginner so please have patience.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Well if the dates are text and not date values then

COUNTIF(A1:A6,"07/2001")
 

ThePencilQueen

Board Regular
Joined
Jun 26, 2002
Messages
109

ADVERTISEMENT

Ok you need to create a pivot table
(I'm going to go through this click by click so if anything seems obvious ignore it and don't be offended...its easier to leave in every step that to miss the one step you really needed)

From the menus at the top click on data, then pivot table, The pivot table wizard will pop up, make sure it has selected the data you need it to look at (it can sometimes get confused if you have a title above the column headers), click next.

Now you need to drag the box on the right that has the date header in it (probably "date") and drop it into the row section.

Then do drag *any* other one of the boxes into the data section (it should say "Count of" whatever the header was if it doesn't double click on it and select count).

Click next, make sure that it will put the table in a NEW worksheet and click finish.

The pivot table will show each date and the number of entries for each date...if you need to sort it by the number of entries click in the very top one and select Data|Sort.

I hope this makes sense...feel free to shout and scream at me if it sounds like I'm talking rubbish.
 

k209310

Active Member
Joined
Aug 12, 2002
Messages
382
Ok thanks for the help but taking one step at a time the count fuction now works.

However the dates are entered as DD/MM/YY i only need the MM/YY info as i was corerectly shown. Is there any way of getting excel to ignore the Days and simply return an answer based on MM/YY? I have tried changing the Cell format but hav had no luck/
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Strange that the format cell is not working. Next way would be to the next column (say b) as such

b1 formula - =if(a1="","",text(a1,"MMYY"))
 

Forum statistics

Threads
1,144,219
Messages
5,723,076
Members
422,477
Latest member
pete101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top