How to correctly use "If" and "and" functions

[amcoope3]

Here is the setup:

I need an "if" statement that works for me.

Cell A1 is the current date of 8/18/2011 (I know this date is in the future. I will eventually use the function "=today()" once I get this logic working)
Cell B1 is the Due date of 7/18/2011
Cell C1 is the actual Completion date of 7/19/2011 which can actually be random and is dependant on what a user enters. Use "7/19/2011" for the purposes of this example.
I want cell D1 to say "OVERDUE" if A1 is greater than B1 and there is nothing entered in cell C1. I want cell D1 to say "COMPLETE" if C1 has any date entered into it despite what A1 has as the date.

Right now I am trying to use the equation in D1 = if(and(C1>0,B1>A1,"COMPLETE","OVERDUE")

I think my problem is within the "C1>0" part of the equation. Basically, if nothing is entered in C1 an we are past the due date then I want it to say OVERDUE. If anything is entered within C1 then I want it to say COMPLETE.

Sorry if this is confusing. Apparently my profile does not allow me to post attachments.

 

[AlphaFrog]

Try something like this...

=IF(C1>0,"Complete",IF(B1>A1,"Overdue",""))

 

[amcoope3]

Thanks for your reply. This equation correctly shows "complete" when I have anything entered in cell C1. However, when C1 is blank then I would expect D1 to show "overdue" but it does not. It does not show any value in D1. I am on excel 2003 so not sure if that is a factor.

 

[amcoope3]

Alright I figured it out. Thanks, AlphaFrog, for getting me on the right path of nested "IF"

I tweaked it a bit and got this equation to work:

=IF(C1>0,"COMPLETE",
IF(B1<A1,"OVERDUE",
IF(B1>A1,"")))

 

[amcoope3]

I dont know why I cannot copy and paste my correctly working function to this post. I paste it in and then when it displays it is all messed up. The equation showing in post from me at 9:25AM is not what I am using. Sorry I cannot get it to show correctly for anyone who wants to see what works but I did get it working.

 

[SuperFerret]

I dont know why I cannot copy and paste my correctly working function to this post. I paste it in and then when it displays it is all messed up. The equation showing in post from me at 9:25AM is not what I am using. Sorry I cannot get it to show correctly for anyone who wants to see what works but I did get it working.

Is it at a point where there is a >? Sometimes the forum messes up on those, usually a space either side sorts it out

 

[amcoope3]

SuperFerret: Yes, I am using less than and greater than signs. I will try reposting. My profile shows that I have "Smilies" on and I thought that might be why it was giving me a headache but I couldnt figure out how to turn "Smilies" off even after going to my profile.

Here is a repost of my equation given your spacing suggestion for the purposes of display:

'=IF(C1 > 0,"complete",IF(B1 < A1,"OVERDUE",IF(B1 > A1,"on schedule")))


OKay, it posted correctly