A further question on dates

[john.bedford3]

If you have a column of dates in the format dd-mmm-yyyy is there a formula
which would count the number of entries where the month is Jan for example.

I have tried COUNTIF(A1:A50,???) but I am not sure what to put in place of
the question marks.

I do not mind using a different date format if this would make it easier.

Thanks

John

 

[Ken Wright]

=SUMPRODUCT(--(MONTH(A1:A100)=1)) BUT, that means a 5 year range would
count ALL January dates regardless of year - is that what you want?

 

[john.bedford3]

Yes Ken, that is exactly what I want and the formula works fine for months
Feb-Dec where I substitute 2-12 respectively in the formula. But for the
month of Jan the formula is counting blank cells in the column as being
January. I do not fully understand the formula ( Why are the two minus signs
or dashes before the MONTH and range? ) so do not see how to amend it for
the month of January.

Any help would be gratefully appreciated.

Thanks

John

 

[Peo Sjoblom]

For January you need to add another condition

=SUMPRODUCT(--(MONTH(A1:A100)=1),--(ISNUMBER(A1:A100)))

it's because that excel sees blank cells as zero and excel dates dtart with
January zero 1900
the unary minuses will convert boolean values of true and false to 1 and 0


http://www.mcgimpsey.com/excel/formulae/doubleneg.html
--


Regards,

Peo Sjoblom

 

[john.bedford3]

Thanks Peo that has done the trick.

Most appreciated.

John