Quote:
Originally Posted by excel@work 85
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 :)