Formula to count workdays if certain criteria is met

Christopher Pretty

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!

Paul Newcome

Try this...

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

Paul Newcome

Try this...

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

markh10

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

@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

@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

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

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

@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.