Due date automation - how can I do this?

ZennerJ

I'm working on a project to automate the flow of processing between multiple staff, each task will have 1-3 days to process [due date] depending on the task.

Is there a way to assign the due date through the automation process? I have it assigning when the previous person has completed their task, but I'm not seeing how to change a date in the change cell automation options.

I've created an Assignment setting worksheet with the number of days to assign thinking I would be able to use the WORKDAY function, but I'm not able to figure out the IF(INDEX/MATCH) formula. I may add another column for the Task - as 2 of us have multiple tasks in the processing of the document, but at the moment I am just trying to get this portion figured out for our processing build.

Any thoughts or pointers would be appreciated!

Andrée Starå

@ZennerJ

Happy to help!

I'm excellent!

You could use the Record a date feature combined with a formula in the main date column.

In short.

• The record a date feature registers the date when a task is assigned
• A formula looks up who it's assigned to and adds the number of days to the above date in the main date cell.

Make sense?

Would that work?

I've developed several different solutions for clients with advanced recurring patterns. (Weekly, Every day except Friday, and similar.

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

Andrée Starå

Hi @ZennerJ

I hope you're well and safe!

• Have you explored using the Record a date feature instead?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

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

ZennerJ

Hi @Andrée Starå,

Thank you for the quick response! I hope you are safe and well ;)

On that option the date entered by the automation is the same date as the item was completed - I need to move the date forward 1 to 3 days depending on the task. So if the task is assigned on Tuesday (3/1/22), it would be due on Wednesday (3/2/22) or Friday (3/4/22) - depending on the " Task Assigned to:" showing at the bottom of the first screenshot. I do have an last modified field/column on the sheet, but each time I tried to use that, it didn't give the right date when trying to add INDEX in the formula to the Due Date field.

I've looked into the Workday formula, but need to get the IF/INDEX/MATCH formula that will determine the number of days to move the date forward to work. I simplified the Assignment list showing only 1 or 3 days, but it will be many more people with more date options - this is just the simplified version for the testing of the build.

I'm basically building my own ticket system that will flow to various people with the assigned due date auto-filling within my department/section. It will be integrated into our primary ticket system in Smartsheet that we're also trying to build, so this feature will be pretty important to our group.

Any thoughts on how to get this portion accomplished?

Best,

Jean Zenner

Andrée Starå

@ZennerJ

Happy to help!

I'm excellent!

You could use the Record a date feature combined with a formula in the main date column.

In short.

• The record a date feature registers the date when a task is assigned
• A formula looks up who it's assigned to and adds the number of days to the above date in the main date cell.

Make sense?

Would that work?

I've developed several different solutions for clients with advanced recurring patterns. (Weekly, Every day except Friday, and similar.

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