Using automation to update a date field

David Dolch
David Dolch ✭✭✭✭✭
in Formulas and Functions

I know you can use the record a date automation to record the current date in a field, but is there any way to update a date field to a chosen value?

We have a date field for the finishing of a task - date finished. If the user selects a Release value from an other drop down column, we want the date finished field to automatically update with the date corresponding to that release. If the release is not selected, then the user can enter whatever date they choose.

We have the Release drop down, which uses a VLOOKUP to populate a helper column called Release Date

We want to create automation that says "When the ER column is changed, change the Date Finished to the Release Date"

That way, if the user selects an ER, then the Date Finished is automatically populated. If not, they can enter their own date.

However, Smartsheet will not let you update a date column with anything except today's date.

Has anybody devised a work around?

Tags:
· Share on FacebookShare on Twitter

Answers

  • Julio S.
    Julio S. Moderator

    Hi @David Dolch,

    As you explain, there isn't currently a way to customize the value that is assigned to a date cell either via the Record a Date or Update Cell automations actions. When you have a moment, please let our Product team know about your feedback by filling in this form, here. Thank you!

    As a possible alternative, you may want to use a formula in the line of =IF(ER@row <> "", [Release Date]@row) in your Date finished column so that the Release Date is auto-populated whenever there is a value assigned in the ER column. To have this formula automatically apply in the rest of the existing rows you may temporarily convert the column to a Column formula and then revert it to be a cell formula (so that the cell is still editable when no ER values are applied) or you can expand it by using the Drag-fill functionality.

    I hope that this can offer some insights and be of help.

    Cheers!

    Julio

    · Share on FacebookShare on Twitter
  • paulherlihey
    paulherlihey ✭✭

    I'm curious if a solution has been created / discovered for this problem as well @Julio S.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!