FORMULA HELP: Where a date is reached and status is 'Complete', resets to a new date and status

Hi everyone,

This forum has been amazingly helpful in the past and I'm hoping you can help me out with a question.

I've been asked to create a formula/automation for monthly/quarterly tasks, whereby if a Baseline Finish date is reached, and the Status row is "Complete", then the date in the Baseline Finish resets to the day after and the Status returns to "Incomplete"

For E.g.

Actual Start is 01/03/23. Baseline Finish date is 31/03/23 and Status is "Complete"

I would like the Actual Start to update to 01/04/23, the Baseline Finish date to update to 30/04/23 and the Status to change to "Incomplete.

This would mean that if the status column is set to "On Track" or "Incomplete" come the Baseline Finish date, the dates won't automatically update.

Grateful for any help!


Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    @HollywoodStu

    This is a tricky one, as the Change Cell Value workflows only update to values entered in the workflow and can't be set via a formula. So the Complete to Incomplete would be fine, but the date would be problematic as you'd really want the date to be set via a formula.

    I am not sure this is achievable in the core product alone. But, I believe we'd be able to write a simple piece of API code to do this for you.

    Please feel free to contact me on debbie.sawyer@smarterbusinessprocesses.com if you'd like to discuss this further.

    Kind regards

    Debbie

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!