Problem with dates in text format (using =COUNTIF)

I have a spreadsheet that has dates copied into it, these dates are then looked up from another sheet to get a figure for how many dates in that month are in a column, such as:

Below is Sheet1 Column A

30/10/2007
30/11/2007
30/11/2007
30/11/2007
30/12/2007
30/12/2007

So Sheet2 uses the COUNTIF function to count any november dates but it does it like this =COUNTIF(Sheet1!A:A,"**/11/2007") but because the dates have been copied in from other spreadsheets sometimes it is set to text format and others to date format but when i try to alter the whole column to text format so the countif function picks all the dates up it obviously turns the dates in 5 number figures which the function doesnt pick up. can someone either tell a different function that will pick it up even if it is in text or date format a way to quickly edit the cells to text format without chnanging the layout of the dates.

If some are text and some are dates, I would recommend changing them all to dates. You can do this by using a helper column, with the formula:

=DATEVALUE(A1)

Then copy the formula down. Next, select all the cells with the formulas, select copy > paste special > values.

Now you can use the following to count the November months:

=SUMPRODUCT(--(MONTH(Sheet1!A1:A1000)=11))

HTH

Jason

That's perfect and if you just use your =SUMPRODUCT(--(MONTH(Sheet1!A1:A1000)=11)) function you dont need to mess with any of the other i do ask one more thing though i understand the =SUMPRODUCT part which counts how many of what we are looking for then the MONTH defines which part of the date we are looking at, inside Sheet1!A1:A1000 and the =11 selects november to be counted but why do we need to put the -- in before the (MONTH(Sheet1!A1:A1000)=11)) ??

=SUMPRODUCT(--(MONTH(Sheet1!A1:A65000)=11))

just so this makes more sense when i type it

cheers in advance though

You typically need the double negative when you are only evaluating one true/false parameter with Sumproduct. Otherwise, it is not necessary. Have a read for yourself:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Jason

ok went to work and it brought up a value error, i relised that if anything else than a date in the format of 07/07/2007 would this formula result in the value error, so i had to check the list and change n/a's and dates like 02/04/07 until it worked but how would i get it to work just looking at the 02/02/2007 format and ignore anything else??