0

I have some requirement to create a formula that evaluates if Fiscal Year >= Current FY, then returns true.

So our fiscal year starts on July 1 and ends June 30.

Example:
6/30/2018, it is in FY18, wheras
7/1/2018, it is in FY19.

Our Fiscal Year field is a picklist field with the following values: 18-19, 19-20, 20-21, 21-22 etc.

I am trying to compare it to the current date, which is Today() to see if it would evaluate to true or not but it has been given me false the whole time.

Anyone willing to help troubleshoot? Thanks!

This is what I have:

IF(
    VALUE(RIGHT(TEXT(Fiscal_Year__c),3)) >=
    IF(
        VALUE(TEXT(MONTH(TODAY()))) >= 6,
        VALUE(LEFT(TEXT(YEAR(TODAY())),2)),
        VALUE(LEFT(TEXT(YEAR(TODAY())+1),2))
    ), "True", "False"
)
2
  • What errors are you getting? It seems like you may have made some copy-paste errors getting your formula into this post.
    – Adrian Larson
    Aug 27, 2021 at 15:54
  • It was returning "false" all the time when I was testing. I figured it out. Thanks for the comment! Aug 27, 2021 at 17:30

1 Answer 1

0

Never mind, I fixed it:

I mixed up the LEFT & RIGHT functions by taking 3 values from the right, and >=6, when it should have been <=6, which gave me "-22" >= "21" resulting in the wrong false/true evaluation.

When it should have been if Fiscal Year = 21-22, then take "22" out of the picklist value to compare to today's date - 8/23/21, which the month is greater than 6, so returns me "22", resulting in 22=22, returns true.

It's corrected now:

IF(
VALUE(LEFT(TEXT(Fiscal_Year__c),2)) >=
IF(
VALUE(TEXT(MONTH(TODAY()))) <= 6,
VALUE(RIGHT(TEXT(YEAR(TODAY())),2)),
VALUE(RIGHT(TEXT(YEAR(TODAY())+1),2))), "True", "False"
)
2
  • You still have a dangling >= and some missing commas. This formula could not possibly compile.
    – Adrian Larson
    Aug 27, 2021 at 17:46
  • Uhm, I tested and it's correct? Fiscal Year = 20-21 for example, takes 20 as the FY >= 8/31/2021, which returns 22 in the inner IF statement. 20>=22, false, returns false. Second example: Fiscal Year = 22-23, takes 22, in the inner If statements, today is 8/31/2021, greater than 6, so returns 22, 22=22, returns true. Aug 31, 2021 at 14:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .