How to create a template smartsheets which all deadline dates change automatically to the new year

Marco de Oliveira

I have a working document that I would like to create a template for so that all deadline dates change the year automatically when the new calendar year begins. The month and day must remain the same, just the year must change based on the new year value.

Anyone know if that is possible please?

Ramzi K

Hi @Marco de Oliveira - try this:

. Create a Reference column (type Date) and put all the deadline dates you need with any year you want. In this case I used 2019.

. Create you Deadline column (type Date) and add the following formula: =DATE(YEAR(TODAY()), VALUE(LEFT(Reference@row, 2)), VALUE(MID(Reference@row, 4, 2)))

This will give you the same date in the current year.

Hope this helps.

Cheers,

Ramzi

Marco de Oliveira

Thank you Ramzi, but I think maybe I gave too little information. I have added a snapshot of the document to make it easier. It is a task tracking document. These tasks are repeated every year. I want to create a blank one so each year I can use that blank one for my staff to perform the tasks and enter the data for that year. The deadlines dates are set to different dates and not today. I hope that makes sense.

Annotation 2020-05-23 141004.png

Andrée Starå

Hi @Marco de Oliveira

To add to Ramzi’s excellent suggestion.

Here’s another option.

Try something like this.

=DATE(YEAR(TODAY()), MONTH(Reference@row), DAY(Reference@row))

It sounds like you should set up a template with the dependency feature.

Not sure I follow, in any case.

Can you describe your process in more detail?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Marco de Oliveira

Hi Andree

Thanks for helping out. I have added more detail per the below.

The first column of dates in each row represents the financial year of each client that we need to prepare the audit for. Each row belongs with each client we have, and each column represents sub-tasks for the audit based on deadlines for each sub-task in a timeline after the financial year end. Each sub-task is in order in the row and there are about 5 deadline dates in each row all linked to the main financial year end date column.

Every year we need to repeat the audit for the same clients, and I don't want to have to change 150 rows of financial year dates. I would like to setup a template smartsheet that I can then just repeat each task every year for the client.

I would like to thus have the smartsheet automatically keep the same month and day that I currently have selected, but change the year every calendar year ie: 28 February but then change the year to match the calendar year that we are in during the future. So, in the year January 2021 to December 2021, I would like my sheet to automatically change all financial year end dates that say 29 February 2020 to 28 February 2021 for example and all those that say 30 June 2020 to 30 June 2021 etc.

So Ramzi's suggestions works, apologies Ramzi. However, his suggestion is a formula that references the financial yearend column so I would need both columns in my sheet. I was hoping to try get something more on a template smartsheet, so I can have all the rows blank for the new financial year and just link them to a smartsheet for that calendar year specifically for a working document. So, his way will work, but I would need to clear all the populated cells every year in each row for the new calendar year. If there isnt another way then i will do it the way Ramzi suggested.

Hope that makes more sense? Sorry, I am brand new to smartsheet, so I don't know all the features and types of sheets yet.

Thanks to both of you for helping out regardless.

Andrée Starå

@Marco de Oliveira

Happy to help!

That makes sense!

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Marco de Oliveira

I have created a copy and deleted the private data.

Thanks very much

Andrée Starå

@Marco de Oliveira

Try something like this. (Update the Yearend dates with my formula and match the month and day)

=DATE(YEAR(TODAY()); 7; 31)

 The same version but with the below changes for convenience.

=DATE(YEAR(TODAY()), 7, 31)

 Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.