Skip to main content
Topics#Data ManagementLauna Saunders의 질문
Launa Saunders (Open to work) 님이 #Data Management에 질문했습니다
2011년 2월 16일 오후 7:10

Calculate the number of business days between date field and today, or clsd date

I have created a custom Date/Time Field labeled "Escalation Date". I need to have a custom formula field display the number of business days a case has been/was opened since the case was escalated; if the custom date field is blank I want the number field to also be blank. 

 

So far I have the following:

 

IF(IsClosed,

 

ROUND(ClosedDate - Escalation_date_c, 0), ROUND((NOW() - Escalation_date_c ),0))

 

It is working great, however I cannot seem to make it so that it only calculates weekdays, and not weekends (even if the escalation date or closed date was on a weekend).
답변 3개
  1. Kirill Yunussov (Mainline Information Systems)
    2011년 7월 15일 오후 4:49
    Here is a formula I had to write to calculate the number of business days between two given dates.   You can adjust it to fit your situation.

     

    5*FLOOR((

     

      CASE(MOD(Actual_Delivery_Date__c-DATE(1900,1,7),7),

     

         1,Actual_Delivery_Date__c,

     

         2,Actual_Delivery_Date__c,

     

         3,Actual_Delivery_Date__c,

     

         4,Actual_Delivery_Date__c,

     

         5,Actual_Delivery_Date__c,

     

         6,Actual_Delivery_Date__c-1,

     

         0,Actual_Delivery_Date__c-2,

     

         Actual_Delivery_Date__c)

     

      -CASE(MOD(IBM_Actual_Ship_Date__c-DATE(1900,1,7),7),

     

         1,IBM_Actual_Ship_Date__c,

     

         2,IBM_Actual_Ship_Date__c,

     

         3,IBM_Actual_Ship_Date__c,

     

         4,IBM_Actual_Ship_Date__c,

     

         5,IBM_Actual_Ship_Date__c,

     

         6,IBM_Actual_Ship_Date__c-1,

     

         0,IBM_Actual_Ship_Date__c-2,

     

         IBM_Actual_Ship_Date__c))/7)

     

    +MOD(

     

       CASE(MOD(Actual_Delivery_Date__c-DATE(1900,1,7),7),

     

         1,Actual_Delivery_Date__c,

     

         2,Actual_Delivery_Date__c,

     

         3,Actual_Delivery_Date__c,

     

         4,Actual_Delivery_Date__c,

     

         5,Actual_Delivery_Date__c,

     

         6,Actual_Delivery_Date__c-1,

     

         0,Actual_Delivery_Date__c-2,

     

         Actual_Delivery_Date__c)

     

      -CASE(MOD(IBM_Actual_Ship_Date__c-DATE(1900,1,7),7),

     

         1,IBM_Actual_Ship_Date__c,

     

         2,IBM_Actual_Ship_Date__c,

     

         3,IBM_Actual_Ship_Date__c,

     

         4,IBM_Actual_Ship_Date__c,

     

         5,IBM_Actual_Ship_Date__c,

     

         6,IBM_Actual_Ship_Date__c-1,

     

         0,IBM_Actual_Ship_Date__c-2,

     

         IBM_Actual_Ship_Date__c),

     

      7)

     

    -IF(

     

      CASE(MOD(IBM_Actual_Ship_Date__c-DATE(1900,1,7),7),

     

        1,1,

     

        2,2,

     

        3,3,

     

        4,4,

     

        5,5,

     

        6,5,

     

        0,5,

     

        0)

     

       <=

     

      CASE(MOD(Actual_Delivery_Date__c-DATE(1900,1,7),7),

     

        1,1,

     

        2,2,

     

        3,3,

     

        4,4,

     

        5,5,

     

        6,5,

     

        0,5,

     

        0),

     

      0,2)

답변을 작성하세요...
0/9000