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

 

[ThePencilQueen]

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

 

[k209310]

Cheers that gives me somewhere to get started.

 

[DonkeyOte]

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]

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]

Well if the dates are text and not date values then

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

 

[ThePencilQueen]

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]

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]

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

 

[k209310]

Thanks for you time. It works fine now.