I am working on a simple system that will produce the name of a task that is coming up within five days of TODAY()'s date. I was successful in getting one single case to populate. However, let's say there are two upcoming assignments due both within a five-day period, I am having trouble trying to get both results to show.
Here is my formula so far:
=IF(AND($W12-TODAY()>=-5,$Y12<>"Yes"),"Invite 1",IF(AND($Z12-TODAY()>=-5,$AB12<>"Yes"),"Invite 2"))
This formula will contain 7-8 if statements for specific dates.
My formula is stopping after the first condition if true...even if the second is true as well. I cannot figure out how for it to populate both results if both conditions are true. And then to get it to populate in a concatenated comma-separated value format.
What am I doing wrong? See the sheet below for reference.
2 Answers
You actually have four different cases to address: 1) Both conditions true return "invite 1 and 2" 2) condition 1 is true return "invite 1", 3) condition 2 is true return "invite 2" and 4) either is true return "". With an IF in excel, the second "ELSE" argument won't execute at all if the condition is true, so the order in which you put the conditions matter:
=IF(AND(daydiff1 >= 5, daydiff2 > 5), "Invite 1" & "; " & "Invite 2", IF(daydiff1 >= 5, "Invite 1", IF(daydiff2 >= 5, "Invite 2", "")))Ps. I use "daydiff" here just as a placemarker for your actual references.
Also, if you want to be fancy, use CHOOSE instead (more efficient since you don't repeat the same comparison operations):
=CHOOSE((daydiff1 >= 5)*1 + (daydiff2 >=5)*2 + 1, "", "Invite 1", "Invite 2", "Invite 1 and 2")Or if you have many dates to check and yes conditions:
="Invite " & MID(IF(AND(daydiff1 >=5, flag1 <> "yes"), " & 1","") & IF(AND(daydiff2 >=5, flag2 <> "yes"), " & 2","") & IF(AND(daydiff3 >=5, flag3 <> "yes"), " & 3","") & ... & IF(AND(daydiff6 >=5, flag6 <> "yes"), " & 6",""), 4, 50)or
=CHOOSE(AND(daydiff1 >= 5, flag1 <> "yes")*2^0 + (daydiff2 >=5, flag2 <> "yes")*2^1 + (daydiff3 >=5, flag3 <> "yes")*2^2 + ... + (daydiff8 >=5, flag8 <> "yes")*2^7 + 1, "", "text1", "text2", "text1,2", "text3", "text1,3", "text2,3", "text1,2,3", "text4", "text1,4", "text2,4", "text3,4", "text1,3,4", "text2,3,4", "text1,2,3,4", ..., "text1,2,3,4,5,6,7,8")The problem with the latter approach is the more tasks you add, the more possible combinations you have. 2 tasks have 2^2=4 text answers to list in CHOOSE. 8 tasks => 2^8 = 256 text answers to list (but CHOOSE has a maximum 254).
I much prefer concatenating individual messages rather than the combinations:
=MID(IF(AND(daydiff1 >=5, flag1 <> "yes"), " & text1","") & IF(AND(daydiff2 >=5, flag2 <> "yes"), " & text2","") & IF(AND(daydiff3 >=5, flag3 <> "yes"), " & text3","") & ... & IF(AND(daydiff8 >=5, flag8 <> "yes"), " & text8",""), 4, 1000)Instead of nesting functions, you can just concatenate them:
=TRIM( IF(AND($W12-TODAY()>=-5,$Y12<>"Yes"),"Invite 1","") & " " & IF(AND($Z12-TODAY()>=-5,$AB12<>"Yes"),"Invite 2","")
)
