Formula to determine Fiscal Year End

[jb00976]

Hi,

I need a formula that will return one of F2019, F2018 or F2017 depending on the columns of year and month. Our year end is March so F2019 will run from April, 2018 to March, 2019; F2018 was April, 2017 to March, 2018; and F2017 was from April, 2016 to March 2017. I was trying to use nested IF/AND/OR as follows

=IF(AND([@Year]=2017,OR([@Month]="April",[@Month]="May",[@Month]="June",[@Month]="July",[@Month]="August",[@Month]="September",[@Month]="October",[@Month]="November",[@Month]="December")),"F2018", IF(AND([@Year]=2018,OR([@Month]="January",[@Month]="February",[@Month]="March",)),"F2018"),IF(AND([@Year]=2018,OR([@Month]="April",[@Month]="May",[@Month]="June",[@Month]="July",[@Month]="August",[@Month]="September",[@Month]="October",[@Month]="November",[@Month]="December")),"F2019", IF(AND([@Year]=2019,OR([@Month]="January",[@Month]="February",[@Month]="March",)),"F2019")))

it works for one fiscal but, once I try to do two or more it says too many arguments. I know there is an easier way to do the formula I believe with VLookup but, I am not well versed in that yet.

Thanks
Jen

 

[Joyner]

Hello, try somethin like this:

=[@Year]+(MATCH([@Month],{"January","February","March","April","May","June","July","August","September","October","November","December"},0)>=4)

 

[Joyner]

Hello, try somethin like this:

=[@Year]+(MATCH([@Month],{"January","February","March","April","May","June","July","August","September","October","November","December"},0)>=4)

If you want an "F" with it then"

="F"&[@Year]+(MATCH([@Month],{"January","February","March","April","May","June","July","August","September","October","November","December"},0)>=4)

 

[jb00976]

thanks, I am not sure how that figures it out but, it works!

 

[Joyner]

thanks, I am not sure how that figures it out but, it works!

You are welcome. It takes the year (a number) and adds either 1 or zero to it based on if the month number is greater than or equal to 4 (April)

MATCH returns the position number of a lookup value. I used an array constant for MATCH to look in {"January","February",...} so for example it will find April in the 4th position, and return "4", So the formula is then is 4 >=4, returning either TRUE or FALSE, and those uses in the formula will be equal to 1 and O respectively. So for example it would be 2017 + TRUE = 2018, or 2017 + FALSE = 2017 (2017 +1, 2017 +0) If you use the "Evaluate Formula" on your Formula ribbon and step through the formula you will see how it is working.