How to auto change Period (DD/MMM/YY22) to next year (DD/MMM/YY23)

Options
Patrick Tan
Patrick Tan ✭✭✭
edited 08/30/22 in Formulas and Functions

Hi All

Is there a way to auto change the date filed when a existing record on 2022 Policies is auto copy to another sheet 2023 Policies?

Million Thanks n advance for your time and advice

Patrick Tan


Tags:

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Patrick Tan

    There may be a few different approaches. Does the 2023 sheet go into affect in 2023 or prior to that. If the sheet won't be used until 2023 you could change the Year portion of the formula to use the function TODAY() instead of the Inception date. YEAR(TODAY()). The Expiring date would become YEAR(TODAY())+1. The caveat is at the end of the year the rows would need to be Copied or Moved (via automation) to another sheet (I call these archive sheets). Doing so captures the values in the cells and not the formulas so the snapshot of data is captured . Otherwise all of your sheets would change dates each year (a formula rolls forward forever). - Because you could automate this step, and make a copy of your archive sheets year to year the same time you're making your new year sheet - this is my preferred option.

    A different approach has a manual step -

    Create a summary sheet field for the Current Year and another field for Current Year +1. Instead of using the TODAY() function in these fields, each year you would manually enter the date you need. Keeping the year you need in a summary field makes it easily accessible. Your field names can be called whatever you like, but I'll use the names above as examples. Also, you can build a formula within your summary Year + 1 that links to the Current Year Field. Then you are only updating the Current Year field.

    For the Inception Date field, use this in the YEAR portion

    YEAR([Current Year]#)


    Expiry Date

    YEAR([Current Year + 1]#)


    Will either of these options work for you? If yes, and you need more clarification, don't hesitate to ask.

    Kelly

  • Patrick Tan
    Patrick Tan ✭✭✭
    Options

    Hey @Kelly Moore

    Thank you for your advice. Let me try it out and see how it works.

    Million thanks again. Cheers


    Patrick

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!