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
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!