Working Days Counter Based on State, Lock # when completed

04/18/19 Edited 12/09/19

I am trying to create a counter that counts the number of days in work of a task, the counter only counts the days in work when the Scrum state of the task changes to sprint . I want the counter to stop counting when the state changes to done and lock that number of days in work (days in work from sprint to done) and still be able to display the number so that i can do averages for reporting out. 

This is what I have so far, it counts when the scrum state is 'Sprint-Fleet' or 'Sprint-Mfg' only; however when the state changes it doesn't count and goes to blank... any tips?

=IF(OR([SCRUM State]472 = "Sprint-Fleet", [SCRUM State]472 = "Sprint-Mfg"), TODAY() - [Sprint Initiation Date]472, "")

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Daniela,

    Unfortunately, it's not possible at the moment, but it's a great idea!

    Please submit an Enhancement Request when you have a moment.

    The TODAY function will always update to the current day.

    A possible workaround would be to use a third-party tool like Zapier or similar.

    Would that work?

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Unless you were to manually input a date that it was changed to "Done". Then you could just add that in to your current formula.

     

    =IF(OR([SCRUM State]472 = "Sprint-Fleet", [SCRUM State]472 = "Sprint-Mfg"), TODAY() - [Sprint Initiation Date]472, IF([SCRUM State]472 = "Done", [Completion Date Column Name]472 - [Sprint Initiation Date]472, ""))

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Paul,

    Yes, of course! There is a manual way! I always try to automate wink

    

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • awesome thank you so much!!!! that certainly helps

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Happy to help!

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

     

    I always try to automate too, but I am limited to working within the confines of SS.

    thinkspi.com

Sign In or Register to comment.