date formula problems

[Michael Fenton]

I have the following formula in a spreadsheet that I am
currently using.
=IF((T2+365)<T2<(T2+1095),"1-3","")
T2 is a date and the cell is formatted to be a date
mm\dd\yyyy

However, when all conditions of the formula are met and 1-
3 SHOULD be displayed it is not. Can someone with fresh
eyes take a look and let me know what I am doing wrong in
this formula.

Thank you!!

 

[Frank Kabel]

Hi
with what value are you trying to compare T2. aybe you meant
try
=IF(AND(TODAY()+365<T2,T2<TODAY()+1095),"1-3","")

 

[AJ Master]

Based on the formula you are using excel will never display "1-3".
Excel uses serial numbers to represent dates.

Let's say that T2 represents 01/01/2004 which is the serial numbe
37987 in excel. Your formula is: IF((T2+365)<T2<(T2+1095),"1-3",""
and excel evaluates it as such:

=if(38352<37987<39082, "1-3","")

In every case excel will leave the cell blank because T2 will ALWAYS b
less.

Are you sure this is what you are trying to have excel evaluate?

A

 

[Myrna Larson]

I agree that the result will always be FALSE, but I'm not sure your
explanation is correct. (I'm not sure mine is, either, for that matter!)

(a) First, T2+365 is NEVER less than T2. If B is positive, A+B is always
greater than A, no matter what the value of A.

(b) OTOH, A is always less than A+C when C is positive. So (T2+365) is greater
than T2 and T2 is less than (T2+1095).

(c) That said, I *think* Excel will evaluate the formula left-to-right. If
that's correct, the first comparison is (T2+365)<T2, which gives FALSE. The
2nd comparison is then FALSE<(T2+1095).

Booleans compare in the same order they sort -- AFTER numbers and text. i.e.
all numbers and text are less than FALSE, and FALSE is less than TRUE. So the
last comparison is, as you said, FALSE, but for because TRUE and FALSE are
both greater than any number.

I think the OP must be looking for the AND operator here, i.e.

IF(AND((T2+365)<T2,T2<(T2+1065)),"1-3","")

But, with that, also, the result is always FALSE, for the reasons given in (a)
and (b) above.

 

[Guest]

Sorry I accidentally posted the wrong formula, my mind
was a bowl of jello yesterday. This is the one I was
referring to only a slight difference.

=IF((T2+365)>B7-T2<(T2+1095),"1-3","")

 

[Frank Kabel]

Hi
try:
=IF(AND(T2+365>B7-T2,B7-T2<T2+1095),"1-3","")

or as an alternative
=IF(AND(2*T2+365>B7,B7<2*T2+1095),"1-3","")

But if you look now at your two conditions:
B7<2*T2+365
B7<2*T2+1095

of of these is not required (the second one). So you could
also write
=IF(2*T2+365>B7,"1-3","")


But maybe your original conditions are not correct?