Pls Help! Automating Rollover of dates Based on change of a status

Mark_Gibbons_1

Hi Everyone,

Can't seem to find much information on this topic can anyone help with the below? Or point me in the right direction.

Trying to create an annual calendar with deadlines.

The calendar includes Monthly, Quarterly & Annual Deadlines.

The idea is that when a team member changes the Tracker column to "Completed":

1) The deadline will be copied to an archive as is (have managed to create workflow for this without much difficulty)

2) The deadline date will automatically Roll into the following period and the tracking column will clear.

To accommodate 2) My initial idea was:

a) add a formula based column called "Deadline +1" that will automatically generate the date of the next period based on the frequency column (done no issue there)

b) Create a Workflow that is triggered when Status changes to "Complete" - Action update date in "Deadline" column with date from "Deadline +1" (Deadline + 1 would then automatically update to the next date).

3) Clear Tracking Column

It does not look like 2) is possible, the workflow will only allow me to pick a specific date and not use data from an existing column.......

Second idea was:

1) We will use a Test Month and Test Year in text/numbers columns to feed into Deadline/formulated date column.

2) We will run a workflow to replace both "Test month" & "Test year" columns based on status changing to "Completed" with column "Month +1" & "Year +1" (which hae their own formulas driven by a Frequency column).

3) Clear Tracking Column

As we can see it will only allow me to replace the cell value with text, once again, I do not have the option to replace a cell with another cell.

I would expect this functionality to be embedded, does anyone have any solutions / suggestions?

Thank you in advance,

Mark

Eric Law

https://community.smartsheet.com/discussion/115538/pls-help-automating-rollover-of-dates-based-on-change-of-a-status

Hello Mark,

You are very close to the solution! What you have is correct, you will need to record a date. The thing you will have to do though is the record a date should be Deadline -1 and then your Deadline column should be =[Deadline -1]@row + ??? depending on your date. There's a few way you can update your new deadline, I would recommend NOT just adding 30 days or 365 as it may get janky. You should specify a specific date and use the DATE formula. For instance, if it was a monthly and we record today 01/17/24, I would make not want the future deadline to be 02/17/24, but set to the 15th of every month, my formula will look like this.

=DATE(IF(MONTH([Deadline -1]) = 12, YEAR([Deadline -1])+1, YEAR([Deadline -1])), IF(MONTH([Deadline -1]) = 12, 1, MONTH([Deadline -1])+1), 15)

Hope that helps!

Mark_Gibbons_1

https://community.smartsheet.com/discussion/comment/414161#Comment_414161

Hi Toufong,

Thank you so much for taking the time to explain the above step by step, I was able to replicate the above model and can confirm it's up and running.

This is great and achieves exactly what I set out to do, would have never achieved this without your help, so thank you again for giving me the step by step and even adding the Date formula which was really helpful!

All the best Kind regards,

Mark

Eric Law

https://community.smartsheet.com/discussion/115538/pls-help-automating-rollover-of-dates-based-on-change-of-a-status

Hello Mark,

You are very close to the solution! What you have is correct, you will need to record a date. The thing you will have to do though is the record a date should be Deadline -1 and then your Deadline column should be =[Deadline -1]@row + ??? depending on your date. There's a few way you can update your new deadline, I would recommend NOT just adding 30 days or 365 as it may get janky. You should specify a specific date and use the DATE formula. For instance, if it was a monthly and we record today 01/17/24, I would make not want the future deadline to be 02/17/24, but set to the 15th of every month, my formula will look like this.

=DATE(IF(MONTH([Deadline -1]) = 12, YEAR([Deadline -1])+1, YEAR([Deadline -1])), IF(MONTH([Deadline -1]) = 12, 1, MONTH([Deadline -1])+1), 15)

Hope that helps!

Toufong Vang

@Mark_Gibbons_1 We have a few collection of sheets that require a similar setup. Below is how we achieve it.

Your current deadline is a dynamic date. It depends on a previously, known deadline. Additionally, you need to know the initial deadline (when the row is first created). Thus, you need 2 helper, date columns. (In this example they are Pre-Deadline and iDeadline.) When you create a new row, the initial deadline must be entered into the column iDeadline.

Assuming that the deadline "day" is persistent, the values in these two columns need to be static. For example, regardless of the frequency, if a deadline is always on the 18th day--e.g., initial deadline of 18-Jan-2024--the new deadline will be 18-Feb-2024 (monthly) or 18-Apr-2024 (quarterly) or 18-Jan-2025 (annual).

Using the DATE() function and adding logic to handle deadlines that rollover to the next year--monthly when deadline is in December, quarterly when a deadline is in October-December--the formula for the Deadline column is:

=DATE(IF(ISBLANK([Pre-Deadline]@row), YEAR(iDeadline@row), IF(OR(AND(Frequency@row = "Quarterly", MONTH([Pre-Deadline]@row) > 9), AND(Frequency@row = "Monthly", MONTH([Pre-Deadline]@row) = 12), Frequency@row = "Annual"), YEAR([Pre-Deadline]@row) + 1, YEAR([Pre-Deadline]@row))), IF(ISBLANK([Pre-Deadline]@row), MONTH(iDeadline@row), IF(Frequency@row = "Annual", MONTH(iDeadline@row), IF(Frequency@row = "Monthly", IF(MONTH([Pre-Deadline]@row) = 12, 1, MONTH([Pre-Deadline]@row) + 1), IF(MONTH([Pre-Deadline]@row) < 10, MONTH([Pre-Deadline]@row) + 3, MONTH([Pre-Deadline]@row) - 9)))), DAY(IF(ISBLANK([Pre-Deadline]@row), iDeadline@row, [Pre-Deadline]@row)))

Next, you will need a separate sheet to set/record a static value in Pre-Deadline. The cleanest way to do this is to create a new sheet (we'll call this SheetB); delete all of the rows except for the primary column; and then copy a row from the source sheet (SheetA) into the new sheet.

After ensuring that SheetB contains no column formulas, configure the column formulas below:

Tracking: ="" (this clears out the "Completed" value)

Pre-Deadline: =Deadline@row (this records the previous deadline date)

In SheetB, configure a workflow automation to immediately route the row back to SheetA.

The copied row to SheetB can be deleted.

Back in SheetA, configure the workflow automation (assuming that SheetC is your archive sheet):

How this works... At the time that a row is initially added, there is not a previous deadline so the value in Deadline is set to whatever is in iDeadline for that row (#1). When Tracking changes to "Completed" (#2), the row is copied to the archive sheet, and then is moved into the processing sheet, SheetB (#3). Almost immediately after being moved into SheetB, the row is moved back into the source sheet, SheetA, with the value in Tracking cleared, the previous deadline set into Pre-Deadline, and the deadline--Deadline--is rolled over to the new date (#4).

Hope this is helpful!

Mark_Gibbons_1

https://community.smartsheet.com/discussion/comment/414125#Comment_414125

Hi Eric,

Thank you for taking the time to respond, I went through your response, but unfortunately could not quite apply , your response into my workings, I think I needed more of a step by step process as I did not understand how this would work "the thing you will have to do though is the record a date should be Deadline -1 ", the record a date uses today's date or a fixed date, and couldn't quite figure out how this would work.

Thank you anyway for taking the time to respond.

Much appreciated.

Mark

Mark_Gibbons_1

https://community.smartsheet.com/discussion/comment/414161#Comment_414161

Hi Toufong,

Thank you so much for taking the time to explain the above step by step, I was able to replicate the above model and can confirm it's up and running.

This is great and achieves exactly what I set out to do, would have never achieved this without your help, so thank you again for giving me the step by step and even adding the Date formula which was really helpful!

All the best Kind regards,

Mark