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

Find more posts tagged with

Formulas

Accepted answers

Jason Albrecht

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), ""))

All comments

Amanda Carta

You don't need the "OR" statement and instead of "-" use ",".

LDLValentine

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.

Jason Albrecht

Hello @LDLValentine,

=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), ""))

LDLValentine

Thanks Jason! That first one was it exactly.

Jason Albrecht

Awesome! Glad it worked 😃

Amanda Carta

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 :)

LDLValentine

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.