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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    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!