Formula to count workdays if certain criteria is met

Options

I have a sheet that is tracking open tickets. In this sheet I have a column that is counting net work days since the ticket has been opened (=NETWORKDAYS([Created Date]@row, TODAY()). I would like to have the column only track networkdays if the ticket is in any status other then Complete or Closed but I keep getting an Unparsable error. Any help would be appreciated!

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this...

    =IF(AND(Status@row <> "Complete", Status@row <> "Closed"), NETWORKDAYS([Created Date]@row, TODAY()))

  • markh10
    markh10 ✭✭
    edited 09/13/22
    Options

    I have a similar issue where I get #INVALID OPERATION.

    I am trying to create a dashboard sheet off my primary ticket tracking sheet. I found out that I cannot create formulas in the dashboard sheet unless I reference to the other sheet, primary ticket tracking sheet, BI Ticket Tracking.

    I want to find out how many days does my ticket stay in Backlog. The column where the calculation/formula is designated as text/number.

    =IF({BI Ticket Tracking Range 5}="Backlog",WORKDAY({BI Ticket Tracking Range 5}, TODAY())

    So if the status is Backlog then calculate the number of days from today to the ticket submission date.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @markh10 You would need to insert a hidden helper column on the source sheet and use a similar formula to calculate that on every row. Then in your metrics sheet you would use a SUM/AVG function depending on exactly what you are wanting to display.

  • markh10
    markh10 ✭✭
    Options

    @Paul Newcome

    Thanks for your response and suggestion.

    1) Where is such a process documented?

    2) So if I understand you correctly, I need to go to my BI Ticket Tracking source sheet, add a Hidden column, then put in my formula of =IF([Ticket Status]@row="Backlog", WORKDAY([Date Submitted]@row, TODAY), 0)

    *NOTE: I got #UNPARSEABLE with this formula.

    And then go to my Dashboard sheet/metric sheet and use that Hidden column calculation result so that I can display it on my dashboard?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    That is correct. And the reason for the error is a missing set of parenthesis after the TODAY function.


    =IF([Ticket Status]@row="Backlog", WORKDAY([Date Submitted]@row, TODAY()), 0)

  • markh10
    markh10 ✭✭
    edited 09/16/22
    Options

    Thanks again @Paul Newcome . I hit another snag. I realize that my Date Submitted cell sometimes is blank so I added:

    NOT(ISBLANK([Date Submitted@row]) check. Now it is:

    =IF(AND([Ticket Status]@row = "Backlog", NOT(ISBLANK([Date Submitted]@row)), WORKDAY([Date Submitted]@row, TODAY()), 0))

    So IF the conditions of Ticket Status is Backlog AND the cell of Date Submitted is Not Blank THEN calculate the days from Date Submitted to Today else return 0.

    I get #INCORRECT ARGUMENT

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @markh10 It looks like it is just a misplaced parenthesis. Take one from the very end and move it to after the NOT/ISBLANK bit to close out the AND statement.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!