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 at 15:54
  • It was returning "false" all the time when I was testing. I figured it out. Thanks for the comment!
    – LZH
    Aug 27 at 17:30
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 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.
    – LZH
    Aug 31 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.