Countif and dates

[ChrisHJH]

Hi,

I have several coumns of data, all of them are dates. I want to add some statistical information in the worksheet telling me which dates are in which range in several of the columns.

I thought countif would be ideal for this so set about using it ... and this is where I need help

=countif(a:a, ">01/01/2011") - this works fine!

=countif(b:b, and(">01/01/2011","<01/01/2012")) - this returned 5 but I could count at least 50 rows which were between the two dates

I have made sure all the rows are formatted as date

Help please!

Thanks

Chris

 

[VoG]

Perhaps

=countif(b:b, ">01/01/2011")-COUNTIF(b:b,">01/01/2012"))

 

[ChrisHJH]

Nope .... same answer of 5, but I can clearly see thats wrong

 

[VoG]

Try

=countif(b:b, ">"&"01/01/2011"+0)-COUNTIF(b:b,">"&"01/01/2012"+0)

 

[ChrisHJH]

Still the same ...

 

[VoG]

Working here

Excel Workbook
	B	C	D
1	05/09/2011		6
2	25/09/2011
3	15/10/2011
4	04/11/2011
5	24/11/2011
6	14/12/2011
7	03/01/2012
8	23/01/2012
9	12/02/2012
10	03/03/2012
Sheet4

 

[RoryA]

Are your dates left- or right-aligned in the cells (if you don't specifically align them to one or the other)

 

[ChrisHJH]

OK, I've had some time to play with this and I'm starting to get a bit closer but nothing anyone has suggested yet works for me

I have a date .... 01/01/2010

Using this in the count if function just doesn't seem to work.

I've been playing with the date value function and I seem to be getting somewhere.

=Datevalue("01/01/2010") returns 40179

This seems to accept the date as an argument and change it to a number so I can then do some simple integer maths.

So, all I need to do is use the column reference .... wrong

I've tried to add quotes in front of the date using all kinds of things but I just can't seem to get a formula to add quotes in front of a cell to give me the date .... "01/01/2010" ... which I can then work with

Can anyone help please?

TIA

Chris

 

[VoG]

By any chance are your dates actually date plus time, formatted to show only the date?

 

[ChrisHJH]

Nope, they are just dates