Skip to main content
Solved

Switch Formula Not Working

Joe_Savage
Forum|alt.badge.img+3

Cannot get this Switch to work and its very simple.  I have a field that has the year of the record stored as a number (ie 2025).  I can easily get this to work with these arguments as a singular IF, but I dont want to nest 5 Ifs. The below will not function, doesnt even give an error, wont even let me save it. Neither Claude nor chatgpt has any suggestions that make sense.  What am I missing??

 

SWITCH(INVOICE_YEAR,
  YEAR(TODAY()), "Current Year",
  YEAR(TODAY())-1, "Previous Year,
  YEAR(TODAY())-2, "Two Years Prior",
  YEAR(TODAY())-3, "Three Years Prior",
  "Older"
)

For this record this works fine: 
 

SWITCH(INVOICE_YEAR, 
  2025, "Current Year",
  2024, "Previous Year",
  2023, "Two Years Prior",
  2022, "Three Years Prior",
  "Older")

 

Best answer by Alexey_Gusev

Hi,
Interesting case.
You have missed quote after “Previous Year”, but that’s not the reason.
It seems like you can use any functions in a first SWITCH argument, but there are some limitations in a list of arguments to compare. Didn’t know that.
You can transform your formula a bit (according to DRY principle)
to get it work

SWITCH(YEAR(TODAY())-INVOICE_YEAR,
0, "Current Year",
1, "Previous Year",
2, "Two Years Prior",
3, "Three Years Prior",
"Older")


 

View original
Did this topic help you find an answer to your question?

5 replies

Mike_AutomaticN
Forum|alt.badge.img+21

I’m not on my computer rn, but my quick gess would be that year() formula is returning a formantes date rather than a number. If that is the case, having the -1, -2, etc might not work. 
please try:

 

 Year(dateadd(today(), -1, “years”) and so on

 

Mike

Joe_Savage
1 person likes this
  • Joe_Savage
    Joe_Savage
Joe_Savage
Forum|alt.badge.img+3
  • Joe_Savage
  • Author
  • Participating Frequently
  • 6 replies
  • March 30, 2025
Mike_AutomaticN wrote:

I’m not on my computer rn, but my quick gess would be that year() formula is returning a formantes date rather than a number. If that is the case, having the -1, -2, etc might not work. 
please try:

 

 Year(dateadd(today(), -1, “years”) and so on

 

Mike

Ty for the reply ! I know it HAS to do with formats or something, but this wasn't it.  

 

Oddly enough, this formula below DOES work. So a logical (and a print) of year(today()) does pass comparison correctly as a number. Ive tried all sorts of tricks to effect the string types, thinking that was the issue and have had no luck. 

 

IF(INVOICE_YEAR=YEAR(TODAY()), "YES", "NO") 

 

Mike_AutomaticN
Forum|alt.badge.img+21

Let me give it a shot tomorrow. Looking forward to it! 

Joe_Savage
1 person likes this
  • Joe_Savage
    Joe_Savage
Alexey_Gusev
Forum|alt.badge.img+23
  • Alexey_Gusev
  • Brainy
  • 1131 replies
  • Answer
  • March 30, 2025

Hi,
Interesting case.
You have missed quote after “Previous Year”, but that’s not the reason.
It seems like you can use any functions in a first SWITCH argument, but there are some limitations in a list of arguments to compare. Didn’t know that.
You can transform your formula a bit (according to DRY principle)
to get it work

SWITCH(YEAR(TODAY())-INVOICE_YEAR,
0, "Current Year",
1, "Previous Year",
2, "Two Years Prior",
3, "Three Years Prior",
"Older")


 

ScottWorld
Joe_Savage
2 people like this
  • ScottWorld
    ScottWorld
  • Joe_Savage
    Joe_Savage
Joe_Savage
Forum|alt.badge.img+3
  • Joe_Savage
  • Author
  • Participating Frequently
  • 6 replies
  • March 30, 2025
Alexey_Gusev wrote:

Hi,
Interesting case.
You have missed quote after “Previous Year”, but that’s not the reason.
It seems like you can use any functions in a first SWITCH argument, but there are some limitations in a list of arguments to compare. Didn’t know that.
You can transform your formula a bit (according to DRY principle)
to get it work

SWITCH(YEAR(TODAY())-INVOICE_YEAR,
0, "Current Year",
1, "Previous Year",
2, "Two Years Prior",
3, "Three Years Prior",
"Older")


 

Awesome! That was the reason, didnt even occur to me that you might not be able to use (some?) functions in the compare argument, but that fixed it! 

Thanks! 

Reply


Most helpful members this week

Terms & Conditions