Display Value IF Between Two Dates
Hello,
I'm trying to create a formula that will display a specific value if a date falls within a certain range. Any assistance would be greatly appreciated.
If “Target Update or Replacement Date” is between 10/01/20 and 09/30/21, then "Budget in Fiscal Year" = “FY 20-21”.
If “Target Update or Replacement Date” is between 10/01/21 and 09/30/22, then "Budget in Fiscal Year" = “FY 21-22”.
If “Target Update or Replacement Date” is between 10/01/22 and 09/30/23, then "Budget in Fiscal Year" = “FY 22-23”.
If “Target Update or Replacement Date” is between 10/01/23 and 09/30/24, then "Budget in Fiscal Year" = “FY 23-24”.
If “Target Update or Replacement Date” is between 10/01/24 and 09/30/25, then "Budget in Fiscal Year" = “FY 24-25”.
Note: The Target Update or Replacement Date is a date column, but the date it displays is being calculated by a formula.
Answers
-
="FY "+IF(MONTH([target update or replacement date]@row) <= 9, RIGHT(YEAR([target update or replacement date]@row) - 1, 2), RIGHT(YEAR([target update or replacement date]@row), 2))+"-"+IF(MONTH([target update or replacement date]@row) <= 9, RIGHT(YEAR([target update or replacement date]@row), 2), RIGHT(YEAR([target update or replacement date]@row) + 1, 2))
I think that should do you. You should try to keep column names short though, it can make writing formulas much more difficult
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!