Trying to create a SWITCH statement with multiple conditions (like a nested IF)

Formulas
1

Hi folks,

I have a SWITCH statement which reads;

SWITCH({REVIEW CADENCE}, “Annual”, DATEADD({LAST REVIEW/SERVICE copy}, 1, “year”), “”)

It basically takes a column formatted to YYYY-MM and adds X numbers of months based on the contents of the review cadence column.

The above works for annual (so 12 months), I have other cadence criteria - example a single month - so need to also do that if cadence is ‘monthly’.

My issue also is that if I use an IF statement, the format adds the DD into things, so I want to lock the output to YYYY-MM and not (with IF) have it give me YYYY-MM-DD (the column doesn’t have the functionality to change date format when it’s set to FORMULA).

Any ideas please?

Thank you

2

This help is from our AI Support

Hi Ian,

Great question! You’re on the right track with SWITCH for multiple conditions. Here’s how to handle both of your requirements:

1. Multiple cadences with SWITCH

You can add multiple pattern/value pairs to SWITCH. Here’s the formula for annual and monthly:

SWITCH(
  {REVIEW CADENCE},
  "Annual", DATEADD({LAST REVIEW/SERVICE copy}, 1, "year"),
  "Monthly", DATEADD({LAST REVIEW/SERVICE copy}, 1, "month"),
  "Quarterly", DATEADD({LAST REVIEW/SERVICE copy}, 3, "month"),
  ""
)

The SWITCH syntax is: SWITCH(expr, pattern1, value1, pattern2, value2, ..., default)

2. Formatting output as YYYY-MM (removing the day)

To strip out the day portion and get just YYYY-MM, wrap your SWITCH result with string functions to extract year and month:

CONCAT(
  YEAR(
    SWITCH(
      {REVIEW CADENCE},
      "Annual", DATEADD({LAST REVIEW/SERVICE copy}, 1, "year"),
      "Monthly", DATEADD({LAST REVIEW/SERVICE copy}, 1, "month"),
      ""
    )
  ),
  "-",
  IF(
    MONTH(
      SWITCH(
        {REVIEW CADENCE},
        "Annual", DATEADD({LAST REVIEW/SERVICE copy}, 1, "year"),
        "Monthly", DATEADD({LAST REVIEW/SERVICE copy}, 1, "month"),
        ""
      )
    ) < 10,
    CONCAT("0", MONTH(
      SWITCH(
        {REVIEW CADENCE},
        "Annual", DATEADD({LAST REVIEW/SERVICE copy}, 1, "year"),
        "Monthly", DATEADD({LAST REVIEW/SERVICE copy}, 1, "month"),
        ""
      )
    )),
    MONTH(
      SWITCH(
        {REVIEW CADENCE},
        "Annual", DATEADD({LAST REVIEW/SERVICE copy}, 1, "year"),
        "Monthly", DATEADD({LAST REVIEW/SERVICE copy}, 1, "month"),
        ""
      )
    )
  )
)

Simpler alternative using LEFT and DATESTR:

Since DATESTR() outputs YYYY-MM-DD format, you can simply take the first 7 characters:

LEFT(
  DATESTR(
    SWITCH(
      {REVIEW CADENCE},
      "Annual", DATEADD({LAST REVIEW/SERVICE copy}, 1, "year"),
      "Monthly", DATEADD({LAST REVIEW/SERVICE copy}, 1, "month"),
      "Quarterly", DATEADD({LAST REVIEW/SERVICE copy}, 3, "month"),
      ""
    )
  ),
  7
)

This gives you exactly “YYYY-MM” format. The DATESTR() function converts your date to “YYYY-MM-DD” string format, and LEFT(..., 7) grabs just the year and month portion.

Hope this helps!

3

So, thanks for this;

In reverse order, option 3 won’t work on our self-hosted instance of Noco, as DATESTR is not available.

I’ve used option 1 in full, as follows (taking into account ALL options in the REVIEW CADENCE field) and outputs have added the date, so I’m seeing YYYY-MM-DD;

SWITCH({REVIEW CADENCE}, “Annual”, DATEADD({LAST REVIEW/SERVICE}, 1, “year”), “Monthly”, DATEADD({LAST REVIEW/SERVICE}, 1, “month”), “Quarterly”, DATEADD({LAST REVIEW/SERVICE}, 3, “month”), “6 months”, DATEADD({LAST REVIEW/SERVICE}, 6, “month”), “18 months”, DATEADD({LAST REVIEW/SERVICE}, 18, “month”), “5 years”, DATEADD({LAST REVIEW/SERVICE}, 5, “year”), “ongoing”, “in line with ISMS”, “always on”, “in line with ISMS”, “n/a”, “n/a”, “”)

I tried option 2 with the above and it’s giving really odd outputs;

CONCAT(
YEAR(
SWITCH(
{REVIEW CADENCE},
“Annual”, DATEADD({LAST REVIEW/SERVICE copy}, 1, “year”),
“Monthly”, DATEADD({LAST REVIEW/SERVICE copy}, 1, “month”),
“Quarterly”, DATEADD({LAST REVIEW/SERVICE copy}, 3, “month”),
“6 months”, DATEADD({LAST REVIEW/SERVICE copy}, 6, “month”),
“18 months”, DATEADD({LAST REVIEW/SERVICE copy}, 18, “month”),
“5 years”, DATEADD({LAST REVIEW/SERVICE copy}, 5, “year”),
“ongoing”, “in line with ISMS”,
“always on”, “in line with ISMS”,
“n/a”, “n/a”,
“”
)
),
“-”,
IF(
MONTH(
SWITCH(
{REVIEW CADENCE},
“Annual”, DATEADD({LAST REVIEW/SERVICE copy}, 1, “year”),
“Monthly”, DATEADD({LAST REVIEW/SERVICE copy}, 1, “month”),
“Quarterly”, DATEADD({LAST REVIEW/SERVICE copy}, 3, “month”),
“6 months”, DATEADD({LAST REVIEW/SERVICE copy}, 6, “month”),
“18 months”, DATEADD({LAST REVIEW/SERVICE copy}, 18, “month”),
“5 years”, DATEADD({LAST REVIEW/SERVICE copy}, 5, “year”),
“ongoing”, “in line with ISMS”,
“always on”, “in line with ISMS”,
“n/a”, “n/a”,
“”
)
) < 10,
CONCAT(“0”, MONTH(
SWITCH(
{REVIEW CADENCE},
“Annual”, DATEADD({LAST REVIEW/SERVICE copy}, 1, “year”),
“Monthly”, DATEADD({LAST REVIEW/SERVICE copy}, 1, “month”),
“Quarterly”, DATEADD({LAST REVIEW/SERVICE copy}, 3, “month”),
“6 months”, DATEADD({LAST REVIEW/SERVICE copy}, 6, “month”),
“18 months”, DATEADD({LAST REVIEW/SERVICE copy}, 18, “month”),
“5 years”, DATEADD({LAST REVIEW/SERVICE copy}, 5, “year”),
“ongoing”, “in line with ISMS”,
“always on”, “in line with ISMS”,
“n/a”, “n/a”,
“”
)
)),
MONTH(
SWITCH(
{REVIEW CADENCE},
“Annual”, DATEADD({LAST REVIEW/SERVICE copy}, 1, “year”),
“Monthly”, DATEADD({LAST REVIEW/SERVICE copy}, 1, “month”),
“Quarterly”, DATEADD({LAST REVIEW/SERVICE copy}, 3, “month”),
“6 months”, DATEADD({LAST REVIEW/SERVICE copy}, 6, “month”),
“18 months”, DATEADD({LAST REVIEW/SERVICE copy}, 18, “month”),
“5 years”, DATEADD({LAST REVIEW/SERVICE copy}, 5, “year”),
“ongoing”, “in line with ISMS”,
“always on”, “in line with ISMS”,
“n/a”, “n/a”,
“”
)
)
)
)

So, I’m close - option 1 is doing what I need but not displaying cleanly.