Automation to change a date column from another date column?

Matt Rasmussen

We have a project intake sheet and a status sheet. When a project estimated completion date is changed on the status sheet, we want to update the intake sheet to reflect the new date. However, Smartsheet Automation can't change a date field with the "Change cell values" action and the "Record a date" action will only update a column with the current date, which doesn't work for us since these are always future dates.

1) Is there a workaround for this?

2) Are there plans to make this work in the near future?

Updating a date column from another date column is no different than updating a text/number column from another text/number column so I can't believe it doesn't already exist.

Jeff Reisman

@Matt Rasmussen You could use an INDEX/MATCH formula on the intake sheet, but it would have to be in a column dedicated to matching the value from the Status sheet. In other words, you can't populate the date in that column in the intake sheet on row creation, it can only be populated based on a value in another column or sheet.

You COULD make it comparative though. Say for instance you have a date (Column A) in the intake sheet that's populated initially, and then you have your dedicated column (Column B) in the intake sheet for most up-to-date date value. In Column B you could use a formula that compares the date from Column A to the date from the Status sheet, and whichever is greater is the date shown. Or you can compare the values and if there's any date value in the field from the Status sheet, use that one. Make sense?

Jeff Reisman

@Matt Rasmussen You could use an INDEX/MATCH formula on the intake sheet, but it would have to be in a column dedicated to matching the value from the Status sheet. In other words, you can't populate the date in that column in the intake sheet on row creation, it can only be populated based on a value in another column or sheet.

You COULD make it comparative though. Say for instance you have a date (Column A) in the intake sheet that's populated initially, and then you have your dedicated column (Column B) in the intake sheet for most up-to-date date value. In Column B you could use a formula that compares the date from Column A to the date from the Status sheet, and whichever is greater is the date shown. Or you can compare the values and if there's any date value in the field from the Status sheet, use that one. Make sense?

Matt Rasmussen

Thanks for the suggestion @Jeff Reisman - I might play around with that. This is an example of my biggest complaint with Smartsheet, since the data is stored in a database Smartsheet should provide more database-like features like this simple column update, lookup lists from other sheets instead of maintaining the same list on multiple sheets, better reports, etc.

Jeff Reisman

@Matt Rasmussen It's really a balancing act with Smartsheet: how to provide the most advanced levels of functionality while maintaining performance in a browser-based cloud application. Being built the way it is, as user-friendly as it is for novices, and performing as well as it does as an online app, it's not going to have the advanced functionality of an MS Access anytime soon. But as the tech for doing more with fewer resources and less processing overhead gets better, we've seen Smartsheet continue to improve over time. Once upon a time sheets were limited to 5,000 rows / 100,000 cells! Two years ago you couldn't use automation to change any cell values. They've added probably a dozen new functions for formulas in the past 2-3 years.

When you have a good idea like this, be sure to submit an enhancement request. These are seen and do go into their lists for consideration.

Andrée Starå

Hi @Matt Rasmussen

I hope you're well and safe!

To add to Jeff's excellent advice/answer.

Here's a possible workaround or workarounds

• The Premium App and Control Center would solve this automatically.
• Also, what I've done in some of my client solutions is to copy the data from the sheets to another so-called helper sheet, and then as long as we had something unique we could use to match the rows, we got the data back to the Intake or similar.

Would any of those options 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!

Matt Rasmussen

Thank you Andrée, but we will just live without this. I have entered an enhancement request for the functionality though.

I understand a limitation on not updating a date column from a text column or vice versa. However, it's an odd decision to allow other date column types to be updated with only the current date rather than a date column from elsewhere.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/333345#Comment_333345

I would kill for automation that let's you update a cell value to a value from another cell or sheet. Or at least one that allows you to insert a specific formula. But we still do have the workaround of using formulas that can apply other formulas based on criteria. Example: =IF(DateA@row > INDEX({Remote Date Column}, MATCH(RowID@row, {Remote RowID Column}, 0)), DateA@row, INDEX({Remote Date Column}, MATCH(RowID@row, {Remote RowID Column}, 0))) English: If the DateA value is greater than the corresponding Date value from the remote sheet, set the value to DateA, otherwise, set the value to the Date from the remote sheet.

Andrée Starå

@Matt Rasmussen

Happy to help!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!