Using multiple conditions in IF OR formula

[Nikiska]

HELP!! I just can't get the following to work into one formula....  basically I have four sets of dates that I need to set status' for to trigger email reminders (1st Pupdate, 2nd Pupdate, 3rd Pupdate, 4th Pupdate). I need a single formula that joins them all together using IF OR or is it IF AND???? I just cant get it to work!!

IF(

  {1st Pupdate},

IF(IS_AFTER(TODAY(), {1st Pupdate}), "🔴 1st Overdue", IF(IS_SAME(TODAY(), {1st Pupdate}), "📆 Due Today", IF(IS_AFTER(TODAY(), DATEADD({1st Pupdate}, -1, 'week')), "⚠️ Due Soon","👌 OK"))))

IF(

  {2nd Pupdate},

  IF(IS_AFTER(TODAY(), {2nd Pupdate}), "🔴 2nd Overdue", IF(IS_SAME(TODAY(), {2nd Pupdate}), "📆 2nd Due Today", IF(IS_AFTER(TODAY(), DATEADD({2nd Pupdate}, -1, 'week')), "⚠️ 2nd Due Soon","👌 OK"))))

IF(

  {3rd Pupdate},

  IF(IS_AFTER(TODAY(), {3rd Pupdate}), "🔴 3rd Overdue", IF(IS_SAME(TODAY(), {3rd Pupdate}), "📆 3rd Due Today", IF(IS_AFTER(TODAY(), DATEADD({3rd Pupdate}, -1, 'week')), "⚠️ 3rd Due Soon","👌 OK"))))

IF(

  {4th Pupdate},

  IF(IS_AFTER(TODAY(), {4th Pupdate}), "🔴 4th Overdue", IF(IS_SAME(TODAY(), {4th Pupdate}), "📆 4th Due Today", IF(IS_AFTER(TODAY(), DATEADD({4th Pupdate}, -1, 'week')), "⚠️ 4th Due Soon","👌 OK"))))

@ScottWorld , @Alexey_Gusev , @TheTimeSavingCo 

[AlliAlosa]

On my phone so I can’t write out the full thing, but I don’t think you need AND() or OR(). It could all be one big nested IF().

You’d want to reverse it, though - start with checking the {4th Pupdate} field. An IF() statement is going to stop evaluating after the first condition that returns true. If you start by evaluating {1st Pupdate}, the formula will never even look at the 2nd, 3rd or 4th dates because once the 1st date passes, it will get stuck at “🔴 1st Overdue”. This is of course assuming that each “Pupdate” is after the next.

[TheTimeSavingCo]

Hmm, you could try combining the IFs with an "&" instead, then in a situation where both the first and second are due soon, the field would output:

⚠️ Due Soon
⚠️ 2nd Due Soon

And so your email would just send that?

[Alexey_Gusev]

Hi,
As @TheTimeSavingCo said, you can just connect them with &
Output will be the status of all 4, so you might want to change a bit, like

IF({1st Pupdate}, "1st update: "&
  IF(IS_AFTER(TODAY(), {1st Pupdate}), "🔴 1st Overdue", IF(IS_SAME(TODAY(),
  {1st Pupdate}), "📆 Due Today", IF(IS_AFTER(TODAY(), DATEADD({1st
  Pupdate}, -1, 'week')), "⚠️ Due Soon","👌 OK")))
&"\n")

[Nikiska]

Thanks for this  @Alexey_Gusev but when I put the formula in which includes all the other pupdates every entry has the 1st update reference in it! it doesn't seem to go past the first IF!! (I have even done the entries backwards as suggested above!)

[Nikiska]

Hi  @AlliAlosa , if you have 5 minutes, please could you spare the time and write it out for me as I still can't get it to work!! LOL!

[Nikiska · Accepted solution]

For those that need something similar... this formula works nicely: Thanks to everyone's input - it all helped 🙂

IF({1st Pupdate}, IF(IS_AFTER(TODAY(), {1st Pupdate}), "✅ ", IF(IS_SAME(TODAY(), {1st Pupdate}), "📆", IF(IS_AFTER(TODAY(), DATEADD({1st Pupdate}, -1, 'week')), "❗","👌 ")))) 

& IF({2nd Pupdate}, IF(IS_AFTER(TODAY(), {2nd Pupdate}), "✅ ", IF(IS_SAME(TODAY(), {2nd Pupdate}), "📆", IF(IS_AFTER(TODAY(), DATEADD({2nd Pupdate}, -1, 'week')), "❗","👌 ")))) 

& IF({3rd Pupdate}, IF(IS_AFTER(TODAY(), {3rd Pupdate}), "✅ ", IF(IS_SAME(TODAY(), {3rd Pupdate}), "📆", IF(IS_AFTER(TODAY(), DATEADD({3rd Pupdate}, -1, 'week')), "❗","👌 ")))) 

& IF({4th Pupdate}, IF(IS_AFTER(TODAY(), {4th Pupdate}), "✅ ", IF(IS_SAME(TODAY(), {4th Pupdate}), "📆", IF(IS_AFTER(TODAY(), DATEADD({4th Pupdate}, -1, 'week')), "❗","👌 "))))

& IF({5th Pupdate}, IF(IS_AFTER(TODAY(), {5th Pupdate}), "✅ ", IF(IS_SAME(TODAY(), {5th Pupdate}), "📆", IF(IS_AFTER(TODAY(), DATEADD({5th Pupdate}, -1, 'week')), "❗","👌 "))))

& IF({6th Pupdate}, IF(IS_AFTER(TODAY(), {6th Pupdate}), "✅ ", IF(IS_SAME(TODAY(), {6th Pupdate}), "📆", IF(IS_AFTER(TODAY(), DATEADD({6th Pupdate}, -1, 'week')), "❗","👌 "))))