IF Formula.... Not sure how to fix it.

minae78 · ‎Sep 13, 2023

I work where following up with my clients are essential.

Right now I have 3 columns in the table that. I'm trying to link together using IF Formula and it's not working so looking for some help here.

First column is "Last Contact Date" which is in "Date" format and next one is "Schedule follow up" which is in "multiple selection" format with 1 day, 3 days, 1 week, 2 weeks, 1 month, 2 months, 3 months 6 months, 1 year. Last column is "Next contact day" where I'm trying to use IF formula to calculate the new date for me and they give me the new date for me. So below is what I have and right now, it's keep saying N/A.

Not sure where I went wrong or how to fix it. Please advise how I could fix this.

Thank you so much in advance.

IF({Schedule follow up}="1 Day",{Last contact date}+1,IF({Schedule follow up}="3 Days",{Lat contact date}+3,IF({Schedule follow up}="1 Week",{Lat contact date}+7,IF({Schedule follow up}="2 Weeks",{Lat contact date}+14,IF({Schedule follow up}="1 Month",{Lat contact date}+30,IF({Schedule follow up}="2 Months",{Lat contact date}+60,IF({Schedule follow up}="3 Months",{Lat contact date}+90,IF({Schedule follow up}="6 Months",{Lat contact date}+180,IF({Schedule follow up}="1+ Year",{Lat contact date}+365,"N/A")))))))))

Sho · ‎Sep 13, 2023

Very deep IF statements are confusing.
Let's use the SWITCH function.

Also, use the Dateadd function to add dates.

IF({Last Contact Date},
  SWITCH({Schedule follow up},
    "1 Day", DATEADD({Last Contact Date}, 1, "day"),
    "1 Month", DATEADD({Last Contact Date}, 1, "Month"),
    "1+ Year", DATEADD({Last Contact Date}, 1, "Year")
  )
)