Looking for suggestions - Date formulas - Utlizing Today formula
Hello All, hope we are all doing fantastic!!!!
So I am working with this Smartsheet that tracks active and non active campaigns that are running for a customer by month. Every 25th of the month, the "Month_Year" Column is updated to the next month. For Example, on Nov 25th, the "NOV_2023" will be changed to "DEC_2023" to show the campaign is running for that month. The problem however is that we have to manually change the "Month_Year" column every 25th of the month for all active customers which is over 1,000. Is there a formula I can create or like a separate Smartsheet to use as a lookup table I can utilize that can automatically update this Month_Year. Another thing to take into consideration is when a campaign has a "cancel" or "not live" in the Change Request Column as it would be deemed inactive and not have any value in the Month_Year Field. A little tricky and confusing question so I do apologize. I think the biggest thing I am looking to get out of this is to make this "Month_Year" column more automated and dynamic vs myself or our team to manually change them.
I tried to create a seperate SS that works as a date converter. The end result needs to be "MONTH_YEAR" but I am unsure on the best route to take to get to that point. Any tips, tricks or info is appreciated
Answers
-
I would suggest a separate sheet that simply has the numbers 1 - 12 in one column and then the month label in another column
1 ....... JAN
2 ....... FEB
3 ....... MAR
so on and so forth.
Then you can use this formula:
=IF(AND([Change Request]@row <> "Cancel", [Change Request]@row <> "Not Live"), INDEX({Reference Table Text Column}, IF(MONTH(TODAY()) = 12, IF(DAY(TODAY())>= 25, 1, 12), IF(DAY(TODAY())>= 25, MONTH(TODAY()) + 1, MONTH(TODAY())))) + "_" + (YEAR(TODAY()) + IF(AND(MONTH(TODAY()) = 12, DAY(TODAY())>= 25), 1, 0)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!