Hello All,

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

Using the "IF" and "AND" conditions (and with some head breaking!:)), I 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 "Send email", that's okay.

However, if I do change I3 to "2" (2 days) and the status is "closed" it says "false". Somewhere down the line, I've made an error. A conflict of 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 the day on which the email was sent and the current day) that have passed since the email has been sent. If three or more days have passed and 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 column 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 length 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 is 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 is "closed" it says "false"." but this isn't the case. If I3 is 2 and status is closed then H3 says "Okay". I presume you meant to say open rather than closed.

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

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

are both covered. BUT, what if the status is Open and days is less than 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 scenarios were met?

Also, just as a side note, =IF() does deal with text if you put it in 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 instead of yours.
=IF(AND(H2=3,F2="Open"),"Send email",IF(AND(H2<=2,F2="Closed"),"Okay"))

Of course this still doesn't fix your initial problem... however if you advise what the email status should show if the other two conditions are 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 the false message.

Like you said:

BUT, what if the status is Open and days is less than 3 or if status is 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 message should say perhaps something like "awaiting update" or "check your email" (the message can be tweaked later).

If the status is closed and days are greater than or equal to 3, the the 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 changed manually (since that has it's own formula "NETWORKDAYS"). I'm aware that 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 more conditions, the formula would work out.

Thanks a lot for your patience and assistance!:)

Try this in cell H2 and copy down as needed:

=IF(AND(I2=3,F2="Open"),"Send Email",IF(AND(I2<=2,F2="Closed"),"Okay",IF(AND(I2< 3,F2="Open"),"Awaiting Update",IF(AND(I2=3,F2="Closed"),"Issue Closed",""))))

It does not use column G with the =LEN() formulas, so you can delete that 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 nothing is filled up in the issue status column, then the email status is also a blank due to the quotes.

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

Many thanks for this! Have a great Sunday:)