Date less than greater than

[jollyjack]

I think I'm going mad! I've searched for the solution everywhere - either i'm not entering the right search parameters or ....I am mad.

I have a list of dates B2:B248. All the cells have been formatted to date type dd/mm/yyyy.

I'm just trying to count the number that are earlier than a certain date.

I thought a COUNTIF function would work! I've tried DATE, DATEVALUE etc.

I can work with the dates ie B2 (which would have 23/08/2007)-1 comes out as 22/08/2007. However when I try if(B2<B3,"lower","higher") it gives me a false reading - even when B2 is earlier than B3.

How can I check if my list of dates is earlier than a specified date please?

this should be so simple yet it's driving me mad.

many thanks.

J.

[mus_mo2049]

you were right you can use countif

i just placed a range of dates in column a for this sample

then the criteria date in c5 and the formula below in d5

=COUNTIF(A1:A37,"<"&C5)

watch out for the " " around the operator

hope that helps

[jollyjack]

Okay I'm trying this...

=COUNTIF(B2:B248,"<"&A250)

...and in cell A250 I have the date 17/08/2007.

I know for a fact that in the selection B2:B248 I have several dates earlier than that in A250, but the result is coming up 0.

Any pointers?

J

[mus_mo2049]

looks like it shoudl be working, can you attach the file or copy it out to a new book and attach??

[jollyjack]

I know!

I just tried your formula on a seperate bit using just a few dates and it worked fine! I'll have to investigate a bit further.

Does it matter what the format of the cells are? Date, General ...etc


J

[mus_mo2049]

it should not make a load of diff. dates are represented as a numeric (think it is the number of days since 1900 or something like that). so in theory if you changed the format to number you would see what it is evaluating against.

today is 39477 for example