Discussions

 View Only
Expand all | Collapse all

I need help to create a formula to calculate a complex duration issue.

  • 1.  I need help to create a formula to calculate a complex duration issue.

    Posted 02-14-2018 22:56

    I need help to create a formula to calculate a complex duration issue.

    I am tracking aging of support tickets from two different systems.

    Therefore, I have a total of 4 time stamps.

     I need an overall duration time that contains the following rules.


    • If Target resolution date and Actual Resolution Date is blank, calculate the number of business days between Date Identified and today's date

    • If Actual Resolution Date is blank, calculate the number of business days between Date Identified and today's date

      If Actual Resolution Date is not blank, calculate the number of business days between Date Identified and Actual Resolution Date




  • 2.  RE: I need help to create a formula to calculate a complex duration issue.

    Posted 02-15-2018 14:57
    • If Target resolution date and Actual Resolution Date is blank, calculate the number of business days between Date Identified and today's date

    • If Actual Resolution Date is blank, calculate the number of business days between Date Identified and today's date

      If Actual Resolution Date is not blank, calculate the number of business days between Date Identified and Actual Resolution Date



    Try this:

    IF(
    isnull([Target resolution date]) and isnull([Actual Resolution Date]), weekdaysub(Today(), [Date Identified]),

    isnull([Actual Resolution Date]), weekdaysub(today(), [date Identified]),

    not isnull([actual resolution date]), weekdaysub([actual resolution date],[date identified]))