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

Tags:

Best Answer

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    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 ",".



  • LDLValentine
    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
    Jason Albrecht ✭✭✭✭✭✭
    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

  • LDLValentine
    LDLValentine ✭✭✭✭

    Thanks Jason! That first one was it exactly.

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

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

  • LDLValentine
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!