Days to Complete Formula for Each Request Type

Options

For a sheet that contains a list of service requests received, I would like to return the number of days between the "Requested Due Date' and the "Actual Completion Date" for each "Request Type." I tried using NETWORKDAY(s) to just return the number of days between the two dates but I keep getting UNPARSEABLE errors. I also tried using an IF formula to provide the number of days based on request type, but that didn't work either. I would be happy to get a formula to work for just the number of days between the two dates, but it'd be even better to get this for each request type.

I also noticed that when I use Smartsheet to generate the formula, the syntax is different from the examples provided on formula web pages. For example, the examples show square brackets, [ ], but the auto-generated formula uses curly brackets, { }. Because of this I'm a little confused about the correct syntax to use. Thanks!

Tags:

Answers

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

    Hi @Kathleen Calvert

    I hope you're well and safe!

    Are you referencing it in the same or a different sheet?

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi Andrée,

    Thank you for the quick response. I am referencing the data from the same sheet. In the sheet, the requestor provides a "Requested Due Date." When the request has been completed, the person assigned to the request enters an "Actual Completion Date." Each request has a "Request Type" associated to it. The "Request Type" represents the different services we offer.

    I want to present this information on a Dashboard to show

    , by request type, the number of days between the "Requested Due Date" and the "Actual Completion Date." Let me know if there's an easier way to present this data.

    I've attached a screenshot, but let me know if you need more info or want me to share my worksheet. I'm just not sure of my employer's policy about sharing.

    Thank you!

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

    @Kathleen Calvert

    Happy to help!

    Try something like this.

    =IFERROR(NETDAYS([Requested Due Date]@row, [Actual Completion Date]@row), "")
    

    Did that work?

    Remember! 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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi Andrée,

    Unfortunately, I'm still getting an unparseable error. I have to log off soon and am out of the office tomorrow. I will follow up on Monday.

    Have a great weekend!

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

    @Kathleen Calvert

    Strange!

    Have a fantastic weekend!

    I'd be happy to take a quick look.

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!