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

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...

Answers

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!