# Counting dates

#### k209310

##### Active Member
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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...

Cheers that gives me somewhere to get started.

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.

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.

Well if the dates are text and not date values then

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

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.

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/

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"))

Thanks for you time. It works fine now.

Replies
2
Views
124
Replies
8
Views
312
Replies
3
Views
517
Replies
4
Views
241
Replies
7
Views
234

Threads
1,219,672
Messages
6,149,616
Members
450,904
Latest member
Gracifer

### 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

### 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