If / Or Statement not working
I have a what I thought was a simple If / Or statement for calculating NETWORKDAYS in Ticketing sheet. Short explanation, when a ticket is opened it is assigned a SLA based on Priority (Critical=1, High=2, Medium=3, Low=4). While the ticket is open the Actual SLA is tracked by NETWORKDAYS. When the closed is closed the tracking is Actual stops and the calculation changes.
Open = Created Date-Today
Closed = Resolved Date-Created Date
Here is the calculation I am using, I cannot seem to find the error. Each separate piece works, but when I combine them I get an "Incorrect Argument Error".
=IF(OR([Tracking Status]@row = "Open", NETWORKDAYS([Created Date]@row - TODAY(), IF([Tracking Status]@row = "Closed", NETWORKDAYS([Resolved Date]@row - [Created Date]@row)))))
Thanks for any help
Best Answer
-
Hello @LDLValentine,
To add to @Amanda Carta's excellent reply and simplified formula, I understand your comment about the 'Closed are left blank' and would offer an edit to the formula (assuming the Status can only be "Open" or "Closed"):
=IF(Status@row = "Open", NETWORKDAYS([Create Date]@row, TODAY()), NETWORKDAYS([Create Date]@row, [Resolved Date]@row))
If there are other options to the Status being Opened or Closed, then the following should also work (recognising Amanda's formula but swapping the 'dates' to produce a positive number of days)
=IF(Status@row = "Open", NETWORKDAYS([Create Date]@row, TODAY()), IF(Status@row = "Closed", NETWORKDAYS([Create Date]@row, [Resolved Date]@row), ""))
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
Answers
-
You don't need the "OR" statement and instead of "-" use ",".
-
Thanks for that, the calculation change now works for all the open! Perfect. The issue still remains of how to 'stop the clock' on those that are closed. I need to be able to effectively track how long it took the help desk to complete a ticket. So from the time a ticket was opened to the time it was closed is a critical factor. With this fix, the Closed are left blank rather than calculating the time between Creation and Resolution Dates.
I do appreciate your assistance. I don't know what I was thinking last night.
-
Hello @LDLValentine,
To add to @Amanda Carta's excellent reply and simplified formula, I understand your comment about the 'Closed are left blank' and would offer an edit to the formula (assuming the Status can only be "Open" or "Closed"):
=IF(Status@row = "Open", NETWORKDAYS([Create Date]@row, TODAY()), NETWORKDAYS([Create Date]@row, [Resolved Date]@row))
If there are other options to the Status being Opened or Closed, then the following should also work (recognising Amanda's formula but swapping the 'dates' to produce a positive number of days)
=IF(Status@row = "Open", NETWORKDAYS([Create Date]@row, TODAY()), IF(Status@row = "Closed", NETWORKDAYS([Create Date]@row, [Resolved Date]@row), ""))
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Thanks Jason! That first one was it exactly.
-
Awesome! Glad it worked 😃
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
I ALWAYS get my date formulas backwards... for some reason it NEVER sticks with me which way to go so I end up testing both ways lol :)
-
Amanda, this entire sheet has sent me through the ceiling and the basement. I do so very much appreciate you and so many others who have jumped in with thoughts, and solutions.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!