Automation: change cell value to a date when triggered

Patrikas

Hello,

I was wondering if it is possible to approach it differently since 'change cell value' to a date is not possible, it is only possible with columns: text/number, dropdown and checkbox in my case. I wanted to have an automation when a person which could be one of two following: contractor or permanent and I want it to fill the date automatically for either: 1 month from start date for 'contractor' and 6 months for 'permanent''.

Thank you!

Andrée Starå

Hi @Patrikas

I hope you're well and safe!

It would be possible by using so-called helper columns. I recently developed something similar for recurring dates for a client.

Would that work/help?

I hope that helps!

Have a fantastic week & Happy Holidays!

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!

Patrikas

Hello @Andrée Starå,

Thank you for your help! How would the text below should look like so I don't have to fill date manually?

Genevieve P.

Hi @Patrikas

You're right, the Change Cell workflow cannot currently update a Date column. You can automatically record a date with a different workflow, but it set's Today's Date (see: Set the Current Date with Record a Date Action)

My suggestion would be to create a formula in that date column instead of using a workflow. You can use an IF statement to add a number of days based on the value in the column that says "contractor" or "permanent".

Try something like this:

=IF([Column Name]@row = "contractor", [Start Date]@row + 31, IF([Column Name]@row = "permanent", [Start Date]@row + 182))

It's easiest to add a number of days instead of dealing with MONTH and YEAR, but there are longer formulas that could add a Month instead of 30 or 31, it depends on what the column needs to be used for.

Cheers,

Genevieve

Patrikas

https://community.smartsheet.com/discussion/comment/314583#Comment_314583

Hi @Genevieve P.,

I tried this and it is something that I have expected. Many thanks for helping me out!

Good luck :)

RC_SG

https://community.smartsheet.com/discussion/comment/314583#Comment_314583

So why is this not possible? Sorry I'm struggling to understand the limitation on why you can't change a cell's date via automation. Like from a technical standpoint I don't understand why it was difficult to implement when the automation feature was created and rolled out, since it's literally just copying data from one cell to another, it shouldn't matter what column type the column is. Especially since we're just copying a date in one cell to a date in another.

My use case is this:

I want to create a form where a person can request a graphic install. These are usually multi-day but sometimes it's a simple that it only takes a single day.

What I want to happen is have a person put a "Install Start Date" and then a checkbox that says "Multiday Install" then if checked it unlocks the "Install End Date" column allowing the person put the end date. However if submitted unchecked, I'd like to have an automation just automatically put the start date into the end date column making it a 1 day event when viewed in calendar view.

This is to prevent redundancy of the person submitting the form from having to re-enter the same exact date twice for single day install requests.

Also, in addition, I'd like to have a "Requested Install Date" column that gets filled by a person submitting the request, and have an automation add that date into the "Scheduled Install Date" column, so that if we have to change the scheduled install date due to a full schedule, we still have the requested install date recorded for reference. Alternatively if we could have the "Scheduled Install Date" populate the requested install date column just so we can know what the original request was before we rescheduled.

Unfortunately the solution here doesn't allow that, as a column becomes un-editable with a column formula, and if we just used a copied formula we'd have to manually create hundreds of rows that we'd manually have to refill after they're used up.

Genevieve P.

Hi @RC_SG

It's really helpful to hear about your specific scenario and sheet set-up. When you have a moment, please let the Product team know about your request by adding your context to this related Product Idea post: Workflow enhancement - copy a column/cell to another column/cell

In the meantime, there are a few options that you could implement using Smartsheet's current functionality. For your first instance I would recommend that instead of a checkbox for if a date is a multiday install, you could have a numerical field asking how many days the install will take.

If it's a single day, they would enter 1. If not, they would enter how many days it will take. This then enables you to have a column formula in your "Install End Date" Column where you can reference the number to create the finish date.

=WORKDAY([Install Start Date]@row, [Days Column]@row) - 1

For your second scenario, @Andrée Starå has a solution where you copy the row to another sheet upon submission, then use an INDEX(MATCH to bring back the original date into a helper column in your current sheet. Here's more information: Lock or Store Date/Value Solution without using Zapier

Cheers,

Genevieve