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

[jollyjack]

today is 39477 for example

........my watch must be wrong!

I've upload a list of dates, your formula etc


J

[mus_mo2049]

def something up with the formats. not 100% sure what but easy enough fix. if you in a new column say C, put in the formula ....

=A1*1

fill that down, then copy and paste special the values over your existing dates and that should fix it.

[jovialtom]

How do the "" around the < affect the formula by the way?

[jollyjack]

Thanks for your help, Mus_mo.

really appreciate it.

J

[jollyjack]

Quick question....

How do I use between two dates using your formula above?

ie count all those that fall between 17/08/2007 - 03/10/2007 ?

Many thanks

J

[ChemistB]

For some reason, I cannot see the formatting you have in Column A (probably because I am using Excel 2000 and you have a different version) however, it looks like your dates are formatted as text. To rectify this, place a 1 in any blank cell. Copy it and highlight your dates in Column A. Right click and Paste Special, then Multiply. This will multiply your dates by 1 which requires Excel to convert them to numbers. Then go back and format them in the appropriate date format.

To do a Greater than, less than, lets put your first date in B1 and second in C1 and your dates in A2:A100. The formula would be

Please Login or Register  to view this content.

ChemistB

[nattasiray]

Suppose the entry date is in range A2:A40. Use this formula

Please Login or Register  to view this content.

Because your input date is not number (It was stored as text.) So I must multiply with 1.