If statement to check if date falls between date range

[mcranda]

I have the following fomula: =IF(AND(M2>i2:M2<J2),"true","false")

The result is returning "0" and I can't figure out why. Columns I,J, and M are all in a date format.

I am trying to figure out is M2 is betwwn i2 and j2.

[Tony Valko]

The formula you posted can not return a value of "0". When correctly written, it can only return either the text string true or false.

As is, it will return a #VALUE! error due to a syntax error.

Replace the colon with a comma.

[mcranda]

I know, but it is returning 0. When I put it in date format it returns 1900/01/00, which is also 0.

[mcranda]

circular reference? It looks like it is giving me a circular reference. I made sure the dates were values and not a formula and it is still giving me a circular reference. How can this be?

[Tony Valko]

Can you post a SMALL sample file that shows the formula returning a 0?

SMALL = less than 50kb.

[mcranda]

Book1.xlsx

Attached is the document.

[Tony Valko]

circular reference? It looks like it is giving me a circular reference. I made sure the dates were values and not a formula and it is still giving me a circular reference. How can this be?

A circular reference means you're entering the formula in a cell that the formula references.

[Tony Valko]

The problem is that you have a syntax error in the formula.

You're using a colon where you should be using a comma.

Enter this formula in C2 and copy down as needed:

=IF(COUNT(A2:B2)<2,"",IF(AND(D2>A2,D2<B2),"true","false"))

Note the comma between A2 and D2.

[mcranda]

Perfect. Thanks

[Tony Valko]

You're welcome!