if formula - multi criteria

[Belinda7237]

I want to be able to place an x in a column only if three criterias are met:


currently in column 0 i have a number of days past due - if column O is
between 21 and 27 days then it meets the criteria. I use this formula for
that:
=IF(O5<21,"",IF(O5<28,"X",""))

In column M I have a date field - if the date in column M is greater
then today then it meets the criteria.

In column Q I have a number value - if the value here is 100,000 or
greater then it meets the criteria

If all three conditions are met then I want an x to go in column
otherwise i want it left blank

Can someone help me with a formula for this?

Thanks!

 

[Roger Govier]

Hi Belinda

=IF(O5<21,"",IF(AND(O5<28,M25>TODAY(),Q25>100000),"X",""))

 

[Duke Carey]

Not tested:

=if(and(o5>=21,o5<=27,m5>today(),q5>=100000),"X","")

 

[Wigi]

Use something like:

=IF(AND(condition1,condition1,condition3),TRUE part,FALSE part)

 

[Belinda7237]

thanks, i am almost there but the date field seems to be giving me an issue:

=IF(O2<21,"",IF(AND(O2<28,M2>TODAY(),Q2>100000),"X",""))

items that have dates prior to today are still getting the X when they dont
meet that condition - i checked the formatting of the date field and its
mm/dd/yyyy which is what i usually use - is there something i should be doing
differently?

 

[Roger Govier]

Hi Belinda

That should work, if your dates are true Excel dates, and not text
representations of the date.
In a spare column, enter
=M2+1
and copy down
Does it give results which are 1 day greater than the dates in column M?
If so, I can't see what the problem is.

If not, then highlight column M>Data Text to
Columns>next>next>Date>M/D/Y>Finish
That should convert text dates to true dates.

I just noticed that you wanted values equal to 100000 or more so the last
term should be
Q2>=100000

 

[Belinda7237]

thanks!

with your tip i found that the field wasn't format as intended.

One othe rquestion - but maybe i should repost as a new question?

What would i add to the formula if i wanted to also indicate that if there
was no date in M then i want to consider it meeting the condition along with
the other to. ie if m2> today or blank, and q2>100000 and 02 is between
21-28 days?