Using if and and for an email tracker

[excel@work 85]

Hello All,

I've been asked by my manager to come up with an email tracker whic
keeps tracks of emails that we send/receive to/from clients.

Using the "IF" and "AND" conditions (and with some head breaking!),
came up with this:

=IF(AND(I2>=3,G2=4),"Send email",IF(AND(I2<=2,G2=6),"Okay"))

Shorter explanation:
The problem:

When I change I3 to "3" (3 days) and the status is "open" it says "Sen
email", that's okay.

However, if I do change I3 to "2" (2 days) and the status is "closed" i
says "false". Somewhere down the line, I've made an error. A conflict o
some kind. Any suggestions or other formulas would be appreciated
Thanks.

(Attached files to avoid confusion).

This is a TLDR, please bear with me:

Longer detailed explanation:
"I2" refers to the number of days (total number of days including th
day on which the email was sent and the current day) that have passe
since the email has been sent. If three or more days have passed an
we've received no replies, it should throw up the message "send email"
If it is lesser than that, then it should say "okay". However...

There is also another condition along with this. I've set another colum
called "Issue Status" which has two categories "Open" and "Closed"
Since the if condition does not consider text, I set another cell "G2
to measure the text length using the LEN formula (Open=4
Closed=6).....

To summarize:

If the number of days is greater than/equal to three and the text lengt
status is 4 (Open), then the result should be "Send email".

If the number of days is lesser than that and the text length status i
6 (Closed), then the result should be "Okay".

Thanks in advance

+-------------------------------------------------------------------
|Filename: Book1.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=386
+-------------------------------------------------------------------

 

[Spencer101]

Hello All

I've been asked by my manager to come up with an email tracker whic
keeps tracks of emails that we send/receive to/from clients.

Using the "IF" and "AND" conditions (and with some head breaking!),
came up with this

=IF(AND(I2>=3,G2=4),"Send email",IF(AND(I2<=2,G2=6),"Okay")

Shorter explanation
The problem

When I change I3 to "3" (3 days) and the status is "open" it says "Sen
email", that's okay

However, if I do change I3 to "2" (2 days) and the status is "closed" i
says "false". Somewhere down the line, I've made an error. A conflict o
some kind. Any suggestions or other formulas would be appreciated
Thanks

(Attached files to avoid confusion)

This is a TLDR, please bear with me

Longer detailed explanation
"I2" refers to the number of days (total number of days including th
day on which the email was sent and the current day) that have passe
since the email has been sent. If three or more days have passed an
we've received no replies, it should throw up the message "send email"
If it is lesser than that, then it should say "okay". However..

There is also another condition along with this. I've set another colum
called "Issue Status" which has two categories "Open" and "Closed"
Since the if condition does not consider text, I set another cell "G2
to measure the text length using the LEN formula (Open=4, Closed=6)....

To summarize

If the number of days is greater than/equal to three and the text lengt
status is 4 (Open), then the result should be "Send email"

If the number of days is lesser than that and the text length status i
6 (Closed), then the result should be "Okay"

Thanks in advance

Hi

You said above *"if I do change I3 to "2" (2 days) and the status i
"closed" it says "false"."* but this isn't the case. If I3 is 2 an
status is closed then H3 says "Okay". I presume you meant to say ope
rather than closed

Basically you need more conditions in the formula. You are onl
covering 2 of 4 possible scenarios.

Status = Open & days greater than or equal to
Status = Closed & days less than 3

are both covered. BUT, what if the status is Open and days is less tha
3 or if status is Closed and days greater than or equal to 3

What Should the email status be if either of these last two scenario
were met?

Also, just as a side note, =IF() does deal with text if you put it i
quotes so you don't need to use the helper column with the =LEN(
formula

So delete column G in your workbook and try using this formula instea
of yours
=IF(AND(H2>=3,F2=\"OPEN\"),\"SEN
EMAIL\",IF(AND(H2<=2,F2=\"CLOSED\"),\"OKAY\")
Of course this still doesn't fix your initial problem... however if yo
advise what the email status should show if the other two conditions ar
met we can help out with a formula if you need

Hope that helps in some way

S :

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[excel@work 85]

Hi

You said above *"if I do change I3 to "2" (2 days) and the status i
"closed" it says "false"."* but this isn't the case. If I3 is 2 an
status is closed then H3 says "Okay". I presume you meant to say ope
rather than closed

Basically you need more conditions in the formula. You are onl
covering 2 of 4 possible scenarios.

Status = Open & days greater than or equal to
Status = Closed & days less than 3

are both covered. BUT, what if the status is Open and days is less tha
3 or if status is Closed and days greater than or equal to 3

What Should the email status be if either of these last two scenario
were met?

So delete column G in your workbook and try using this formula instea
of yours
=IF(AND(H2>=3,F2=\"OPEN\"),\"SEN
EMAIL\",IF(AND(H2<=2,F2=\"CLOSED\"),\"OKAY\")
Of course this still doesn't fix your initial problem... however if yo
advise what the email status should show if the other two conditions ar
met we can help out with a formula if you need

Hope that helps in some way

S :

Thanks! and yes, I'm aware that the formula has problems, hence th
false message

Like you said

BUT, what if the status is Open and days is less than 3 or if status i
Closed and days greater than or equal to 3

yes, I had that thought too....Okay here goes

If the status is open and days are lesser than three, then the messag
should say perhaps something like "awaiting update" or "check you
email" (the message can be tweaked later)

If the status is closed and days are greater than or equal to 3, the th
message should be "Issue Closed"

I've actually removed the drop down box for "Open" and "Closed" (FYI)

Under no circumstances should the total no of days column be change
manually (since that has it's own formula "NETWORKDAYS"). I'm aware tha
by changing that (just tried it out), the "FALSE" error would go away
However, this would be nothing short of cheating the program ;

Hopefully everything is covered and hoping that with a few mor
conditions, the formula would work out

Thanks a lot for your patience and assistance!:

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[excel@work 85]

Try this in cell H2 and copy down as needed:

=IF(AND(I2>=3,F2="Open"),"Sen
Email",IF(AND(I2<=2,F2="Closed"),"Okay",IF(AND(I2<3,F2="Open"),"Awaitin
Update",IF(AND(I2>=3,F2="Closed"),"Issue Closed",""))))

It does not use column G with the =LEN() formulas, so you can delet
that row AFTER you paste this formula in.

Hope that's done what you want.

S.

Perfect! the blank quotes at the end is helpful. For example, if nothin
is filled up in the issue status column, then the email status is also
blank due to the quotes.

Seems to be working fine, if there are any issues, will report back t
this thread.

Many thanks for this! Have a great Sunday:

+-------------------------------------------------------------------
+-------------------------------------------------------------------