CODEWITHSUNDEEP
excelif-statementexcel-formula
JMIZE
JMIZE

Reputation: 11

IF statement with three conditions

enter image description here

column V is homework actual finish date
column W is required finish date

condition 1: if V is not blank, return value "ok"
condition 2: if "V is blank" and "W is blank", return value "ok"
condition 3: if "V is blank" and "W>=today()", return value "overdue"

I'm only able to combine condition 1 and 3 in my IF formula: 

=IF(NOT(ISBLANK(V2)),"ok",IF(W2<=TODAY(),"over due","ok"))

Could anybody help me add condition two into my formula?

thanks for everybody's help/ im really new to stackoverflow. and i have already learned something from everybody.

btw i made a typo in my condition 3, it should be "W<=today()" instead "W>=today()"

thanks

Upvotes: 0

Views: 2106

Answers (4)

Grade &#39;Eh&#39; Bacon
Grade &#39;Eh&#39; Bacon

Reputation: 3823

This is quite simple with one AND statement. First, redefine the logic you need for yourself: there are only two outcomes you care about: an assignment is unfinished and overdue, OR an assignment is finished/not yet due. So to write our IF statement, check only whether the assignment is unifinished and overdue. In any other case, the status will be the same.

Edit per comment

=IF(AND(ISBLANK(V1),W1<=TODAY(),W1>1),"overdue","ok")

Upvotes: 0

Display name
Display name

Reputation: 1542

Try this.

=IF(NOT(ISBLANK(V2)),"ok",IF(ISBLANK(W2), "ok",IF(AND(ISBLANK(V2),W2 >= TODAY()),"Over Due", "Error")))

As tip for future macros, try building them up a little at a time. For IF statements it can get tricky but this may help.

Write the first condition like this with 'Other Value' as a place holder.

=IF(NOT(ISBLANK(V2)),"ok","Other Value")

Once you have that bit working the way you like it then put in the false condition like this.

=IF(NOT(ISBLANK(V2)),"ok",IF(ISBLANK(W2),"ok","Other Value"))

And then finally add your last condition.

Upvotes: 0

Joseph Marino Tripp
Joseph Marino Tripp

Reputation: 63

Let me see;

Your decision tree is missing one possible output; when V is blank, W !is blank and W<=today()

once you tell us what output means to you, we can easily help you.

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37029

=if(  and(  not(isblank(v2)), not(isblank(w2))  ), "ok",
    if(  not(isblank(v2)), "ok",
       if(  and(  isblank(v2),  w2 >= today()  ), "overdue", "ok"  )
    )
 )

Paste this directly into the formula box and see how it works.

Upvotes: 2

Related Questions