Set the Start Date and End Date Based On When A Task Was assigned

I want to set the start date for a task when someone is assigned to the task using "Record A Date" in a workflow.

I also want to assign the due date 30 days from the start date.

I know how to use the "Record A Date" workflow and I know how to add 30 days to a date. So please don't explain them to me.

The problem I am having it seems you cannot use a formula or a workflow to set the Start or Finished dates. Is that the case or am I missing something?

To be clear these are the dates that I am trying to set by a formula


Best Answer

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭
    edited 01/05/22 Answer ✓

    Hey Sean -

    You are correct in that you can't add a formula to columns that you've selected for your "Date range display" date columns. This frustrates me as well!

    Unless I'm mistaken, there's not a "native" way within Smartsheet to do exactly what you're wanting using those "date range display" columns.

    You could always create additional date column, and then this would work. Sadly, you won't be able to utilize your "Date range display" columns. With some additional date columns, you could utilize the "Record a date" workflow for a date column, then have a second date column with a "Column Formula" that is the first created date column + 30 days.


    I'm curious if there is any workaround available to implement this with the date range display columns.

    I know that what you're wanting to do could be implemented using a service like Microsoft's Power Automate (or Zapier, or Automate.io, etc.) then triggering the Smartsheet API. It'd be a little complex but it'd be doable. If you're not savvy on that I could likely lend a hand - but keep in mind that those sorts of automation services usually come with a subscription fee. If your org is already using one of those services, I could show you how to link it all together; no charge.

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭
    edited 01/05/22 Answer ✓

    Hey Sean -

    You are correct in that you can't add a formula to columns that you've selected for your "Date range display" date columns. This frustrates me as well!

    Unless I'm mistaken, there's not a "native" way within Smartsheet to do exactly what you're wanting using those "date range display" columns.

    You could always create additional date column, and then this would work. Sadly, you won't be able to utilize your "Date range display" columns. With some additional date columns, you could utilize the "Record a date" workflow for a date column, then have a second date column with a "Column Formula" that is the first created date column + 30 days.


    I'm curious if there is any workaround available to implement this with the date range display columns.

    I know that what you're wanting to do could be implemented using a service like Microsoft's Power Automate (or Zapier, or Automate.io, etc.) then triggering the Smartsheet API. It'd be a little complex but it'd be doable. If you're not savvy on that I could likely lend a hand - but keep in mind that those sorts of automation services usually come with a subscription fee. If your org is already using one of those services, I could show you how to link it all together; no charge.

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • Sean Corcoran
    Sean Corcoran ✭✭✭✭

    @Brett Wyrick Thanks for the reply, I was afraid it was not possible. For this particle sheet / project I don't really need to use Smartsheet features related to the predecessors and Gantt view so I will just use two new date fields. I just was trying to use what Smartsheet gives me and thinking what if I need this in the future and I can't use an added date filed. - Thanks again

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!