Formula to count workdays if certain criteria is met
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
-
Try this...
=IF(AND(Status@row <> "Complete", Status@row <> "Closed"), NETWORKDAYS([Created Date]@row, TODAY()))
Answers
-
Try this...
=IF(AND(Status@row <> "Complete", Status@row <> "Closed"), NETWORKDAYS([Created Date]@row, TODAY()))
-
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.
-
@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.
-
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?
-
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)
-
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
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!