I'm trying to determine the interval between two events in a process, only counting business hours.

I'm trying to determine the interval between two events in a process, only counting business hours. It's for a metric based on minutes, so I can't take advantage of calworkdays or calworkhours. I finally got a convoluted equation to work, but it seems to be adding time rather than removing it to resolve the business time issue. Any assistance would be appreciated.

ITO Notify Duration pseudocode


If request required approval
          If Approval Timestamp and Acceptance TS are NOT blank
                    If approval and acceptance occurred on the same day (take holidays into account)
                              Acceptance TS - Approval TS
                    Else
                              Acceptance TS - Approval TS -
                              # of working days between dates * 14 hrs -
                              # of nonworking days between dates * 24 hours
          Else null
Else
          If Submittal TS and Acceptance TS are NOT blank
                    If submittal and acceptance occurred on the same day (take holidays into account)
                              Acceptance TS - Submittal TS          
                    Else
                              Acceptance TS - Submittal TS -
                              # of wo...

OriginalPostID-77985

  Discussion posts and replies are publicly visible

Parents
  • ...rking days between dates * 14 hrs -
                                  # of nonworking days between dates * 24 hours
              Else null
                        
                        
    ITO Notify Duration v2

    Approval flag = pv!pvSupervisorApproval
    Approval Timestamp = pv!pvSrq_Approved_DateTime (only used if Approval flag is set)
    Acceptance TS = pv!pvSrq_Notified_DateTime
    Submitted TS = pv!pvSrqSubmitted_datetime (only used if Approval flag is NOT set)
    CST_State_Holidays = List of state holidays in MM/DD/YY format
    Working Days are Mon-Fri excluding holidays

    If (pv!pvSupervisorApproval = "Approve",(If(or(isnull(pv!pvSrq_Approved_DateTime),isnull(pv!pvSrq_Notified_DateTime)),"",(If(todate(pv!pvSrq_Approved_DateTime)=todate(pv!pvSrq_Notified_DateTime),pv!pvSrq_Notified_DateTime - pv!pvSrq_Approved_DateTime,pv!pvSrq_Notified_DateTime - pv!pvSrq_Approved_DateTime - (intervalds(networkdays(todate(pv!pvSrq_Approved_DateTime),todate(pv!pvSrq_Notified_DateTime),CST_State_Holidays) - 1 * 14,0,0)) - (intervalds(days360(todate(pv!pvSrq_Approved_DateTime),to...
Reply
  • ...rking days between dates * 14 hrs -
                                  # of nonworking days between dates * 24 hours
              Else null
                        
                        
    ITO Notify Duration v2

    Approval flag = pv!pvSupervisorApproval
    Approval Timestamp = pv!pvSrq_Approved_DateTime (only used if Approval flag is set)
    Acceptance TS = pv!pvSrq_Notified_DateTime
    Submitted TS = pv!pvSrqSubmitted_datetime (only used if Approval flag is NOT set)
    CST_State_Holidays = List of state holidays in MM/DD/YY format
    Working Days are Mon-Fri excluding holidays

    If (pv!pvSupervisorApproval = "Approve",(If(or(isnull(pv!pvSrq_Approved_DateTime),isnull(pv!pvSrq_Notified_DateTime)),"",(If(todate(pv!pvSrq_Approved_DateTime)=todate(pv!pvSrq_Notified_DateTime),pv!pvSrq_Notified_DateTime - pv!pvSrq_Approved_DateTime,pv!pvSrq_Notified_DateTime - pv!pvSrq_Approved_DateTime - (intervalds(networkdays(todate(pv!pvSrq_Approved_DateTime),todate(pv!pvSrq_Notified_DateTime),CST_State_Holidays) - 1 * 14,0,0)) - (intervalds(days360(todate(pv!pvSrq_Approved_DateTime),to...
Children
No Data