Looking for suggestions - Date formulas - Utlizing Today formula

Options

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 ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!