How do I make several IF (nested if) to work for the following scenario?

Kareen Caole

Hi,

I need help with a nested if.

Given:

Date Won: Aug 31, 2018

Sales Metrics Range 1 : July 1, 2018

Sales Metrics Range 2 : June 30, 2019

Formula working at :

=IF(AND([DATE WON]@row >= {Sales Metrics Range 1}, [DATE WON]@row <= {Sales Metrics Range 2}), "FY19")

If my Sales Metrics range changes to different financial years, how can I integrate and add it to the above working formula and make several ifs and change FY 19 to FY20 and so on.. IFS

=IF(AND([DATE WON]@row >= {Sales Metrics Range 1}, [DATE WON]@row <= {Sales Metrics Range 2}), "FY19"), IF(AND([DATE WON]@row >= {Sales Metrics Range 1}, [DATE WON]@row <= {Sales Metrics Range 2}), "FY20") and so on...

Paul Newcome

A nested if can become rather bulky and will need constant updating for this. Try something like this to build off of the date directly.

="FY" + RIGHT(YEAR([DATE WON]@row) + IF([DATE WON]@row >= DATE(YEAR([DATE WON]@row), 7, 1), 1, 0), 2)

First we output "FY" and grab the right 2 digits of the year of Date Won. Then we say that if the Date Won is greater than or equal to July 1 of that year, add 1 to the year.