if formula - multi criteria

B

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!
 
B

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?
 
R

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
 
B

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top