Shorten a Nested If Formula for Fiscal Year

Question

I created a formula with nested if statements. Since the conditions are similar, is there a way to write it elegantly?

Fiscal_Year__c is a picklist field with values such as 20-21, 21-22, 22-23 etc.

Fiscal Year is as follows: July 1, 2021 - June 30, 2022 = FY 21-22 July 1, 2022 - June 30, 2023 = FY 22-23

We are comparing it against current fiscal year to give it a dynamic name such as "Two FY Ago", "Last FY", "This FY" and "Next FY" for better reporting since those fiscal year records do not have fields that can allow relative date ranges.

IF(
AND(
VALUE(TEXT(MONTH(TODAY()))) < 7,
TEXT(Fiscal_Year__c) = TRIM(RIGHT(TEXT(YEAR(TODAY())-3), 2)) & "-" & TRIM(RIGHT(TEXT(YEAR(TODAY())-2), 2))),
"Two FY Ago",
/*Current FY = 21-22, Two FY Ago = 22-3=19, 22-2=20, yields 19-20.*/

IF(AND(
VALUE(TEXT(MONTH(TODAY()))) < 7,
TEXT(Fiscal_Year__c) = TRIM(RIGHT(TEXT(YEAR(TODAY())-2), 2)) & "-" & TRIM(RIGHT(TEXT(YEAR(TODAY())-1), 2))),
"Last FY",
/*Current FY = 21-22, One FY Ago/Last FY = 22-2 =20, 22-1=21, yields 20-21.*/

IF(AND(
VALUE(TEXT(MONTH(TODAY()))) < 7,
TEXT(Fiscal_Year__c) = TRIM(RIGHT(TEXT(YEAR(TODAY())-1), 2)) & "-" & TRIM(RIGHT(TEXT(YEAR(TODAY())), 2))),
"Current FY",
/*Current FY = 21-22, Current FY = 22-1 = 21, 22=22, yields 21-22.*/

IF(AND(
VALUE(TEXT(MONTH(TODAY()))) < 7,
TEXT(Fiscal_Year__c) = TRIM(RIGHT(TEXT(YEAR(TODAY())), 2)) & "-" & TRIM(RIGHT(TEXT(YEAR(TODAY())+1), 2))),
"Next FY",
/*Current FY = 21-22, Next FY = 22=22, 22+1=23, yields 22-23.*/

IF(
AND(
VALUE(TEXT(MONTH(TODAY()))) > 6,
TEXT(Fiscal_Year__c) = TRIM(RIGHT(TEXT(YEAR(TODAY())-2), 2)) & "-" & TRIM(RIGHT(TEXT(YEAR(TODAY())-1), 2))),
"Two FY Ago",
/*Current FY = 22-23, Two FY Ago = 22-2=20, 22-1=21, yields 20-21.*/

IF(AND(
VALUE(TEXT(MONTH(TODAY()))) > 6,
TEXT(Fiscal_Year__c) = TRIM(RIGHT(TEXT(YEAR(TODAY())-1), 2)) & "-" & TRIM(RIGHT(TEXT(YEAR(TODAY())), 2))),
"Last FY",
/*Current FY = 22-23, One FY Ago/Last FY = 22-1 =21, 22=22, yields 21-22.*/

IF(AND(
VALUE(TEXT(MONTH(TODAY()))) > 6,
TEXT(Fiscal_Year__c) = TRIM(RIGHT(TEXT(YEAR(TODAY())), 2)) & "-" & TRIM(RIGHT(TEXT(YEAR(TODAY())+1), 2))),
"Current FY",
/*Current FY = 22-23, Current FY = 22-23, 22=22, 22+1=23, yields 22-23.*/

IF(AND(
VALUE(TEXT(MONTH(TODAY()))) > 6,
TEXT(Fiscal_Year__c) = TRIM(RIGHT(TEXT(YEAR(TODAY())+1), 2)) & "-" & TRIM(RIGHT(TEXT(YEAR(TODAY())+2), 2))),
"Next FY",
/*Current FY = 22-23, Next FY = 23-24, 22+1=23, 22+2=24, yields 23-24.*/

""))))
))))

Answer 1

Using your picklist Fiscal_Year__c field picklist API names, always as strings of the form 'yy-yy', where yy is the last two digits of a year in the range 2000 to 2099, if you only care about a limited range of fiscal years, plus that I've understood what you're trying to do, I think you should get the right effect from something like this:

CASE(2000 + VALUE(LEFT(TEXT(Fiscal_Year__c), 2)) - YEAR(ADDMONTHS(TODAY(), -6)),
  -2, 'Two FY Ago',
  -1, 'Last FY',
  0, 'This FY',
  1, 'Next FY',
  2, 'Two FY From Now',
  IF(2000 + VALUE(LEFT(TEXT(Fiscal_Year__c), 2)) < YEAR(TODAY()), 'A while ago', 'A while away'))

This takes today's date and subtracts 6 months to remove the fussy July to June FY alignment, bringing it to January to December instead. We can then compare the fiscal year start (the earlier year) with this, via use of a CASE to select an appropriate description based on the limited set of offsets you listed. I also added an "else" case that basically provides a way to handle fiscal year offsets not catered for otherwise. Note that the "else" part doesn't need to worry about the 6 month offset since we already covered the range of fiscal years where this would be important.