Update task numbers to include year reference & restart each year

Sarah_D

Hi - each task, and related subtask, in our office is logged in Smartsheet and allocated a task / subtask number as shown below.
I would like to add a 2 digit year prefix at the beginning and have the task numbers restart each calendar year i.e. the last task of 2024 would be Task # 24-304, and subtask 24-304.4 would be the last step (rather than #304 and #304.4 in the current sheet layout), and the first task for this year would be 25-1.

I have tried a number of different prefix, suffix, & other helper columns. I have a column for the 2 digit year identifier, derived from the row creation date, but am struggling to restart the Parent task number at the start of the calendar year, and combine the various fields into YY-ParentTask.ChildSubtask
Thanks in advance!

che.rabajante

Hello @Sarah_D

Please try this:

1. Create an Autonumber System Column: Add a new column named RowID and set it to Autonumber. This will automatically generate a unique ID for each row.
   
2. Add the Level Column with Formula: =COUNT(ANCESTORS()) + 1
   
   This formula calculates the level of each task in the hierarchy (parent tasks will have a level of 1, subtasks will have higher numbers based on their depth).
3. Task Number Formula:Add a column named Task Number and use the following formula:
   
4. Drag Down the Formula: Once you enter the formula in the first row, drag it down to the succeeding rows. This will automatically apply the formula to all the tasks in the sheet.
5. Automatic Population for Newly Added Rows: As new rows are added to the sheet, the formula will automatically calculate and populate the Task Number for both parent and subtask rows, based on the hierarchical structure.

Hope this helps.

che

Sarah_D

Thank you so much @che.rabajante, that's really helpful.

The format of the Task # is now exactly as we need it, however the parent task numbering doesn't restart for tasks added in 2025 and instead continues on from 2024 (i.e. the first task for this year is 25-305 rather than 25-1.
Do you have any suggestions for restarting the numbering each calendar year?

che.rabajante

Hello @Sarah_D

Is the value 305 manually entered in this sheet, or is it being pulled from a separate sheet?

Sarah_D

Hi @che.rabajante - it's being autopopulated, it's the Task # derived through the steps you outline above. For example, the last task created in 2024 now appears as 24-304, and the first task created in 2025 appears as 25-305 rather than 25-1

I can find a similar question here, but am struggling to make the suggested solution work with the format (i.e. 25-1.1) I'm seeking.

Georgie

Hi @Sarah D,

I tested out editing @jmyzk_cloudsmart_jp's solution to get the format you’re looking for and was able to do so by changing the formulas. Below are the columns you’ll need and the formulas you’ll need in the columns:

• Created: (no formula) A system created date column - this updates when you save the sheet and will provide the timestamp that each row was created.
• Row ID: (no formula) An auto number column with no changes (as suggested by @che.rabajante) - so that it provides the number of the row in the sheet.
• Ancestors: =COUNT(ANCESTORS())
• Year: =VALUE(RIGHT(YEAR(Created@row), 2)) Note: this is the same formula as in the original solution, I’ve just edited it to use the Created column instead of the Date column.
• IDA0 (you can change the name as desired): =IF(Ancestors@row = 0, RIGHT(MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], Ancestors:Ancestors, Ancestors@row, Year:Year, Year@row)), 4))
• IDA1 (you can change the name as desired): =IF(Ancestors@row = 1, MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], Parent:Parent, Parent@row)))
• Parent: =IF(Ancestors@row > 0, PARENT([Primary Column]@row))
• JOB ID or Task # (you can change the name as desired): =Year@row + "." + IF(Ancestors@row = 0, [IDA0]@row, PARENT([IDA0]@row) + "." + [IDA1]@row)

Your Task # column should then follow the format you’re looking for, as seen in my test sheet below:

You can see that task 1 and its child were created in 2024 and tasks 2 and 3 and their child rows were created in 2025, so the job ID for row 3 starts with 25 and returns to 1 as it's the first task in 2025 ("task 2"). 

Does that work for you?

Georgie