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

Patrick Tan

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

Kelly Moore

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

uyus62

https://community.smartsheet.com/discussion/comment/341523#Comment_341523

It works for me thanks.

Patrick Tan

Hey @Kelly Moore

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

Million thanks again. Cheers

Patrick