1 Reply Latest reply on Nov 2, 2018 1:38 PM by Deepak Rai

    Calculating Business Days (No Weekends/No Holidays)

    albert wong

      Hey Everyone,

       

      I'm sure this has been posted before but just thought i'd share a calculation I use to calculate business days as it's useful for coming up with averages/run rates based on month.

       

           1) Create first Calc and name it: 'First Day (current month)'

               Enter the Following Calc:

      DATE(DATETRUNC('month',TODAY()))

       

           2) Create second Calc and name it: 'Last Day (current month)'

                Enter the following Calc:

      DATETRUNC('month',DATEADD('month',1,TODAY()))-1

       

           3) Create a third Calc and name it: 'Business Days'

                Enter the following Calc: NOTE - this automatically subtracts 1 day from each of the following months (January, May, July, September, November, December) which represent (New Years Day, Memorial Day, Independence Day, Labor Day, Thanksgiving and Christmas Day). You can adjust the months to your liking....

       

      IF (CONTAINS(DATENAME('month',TODAY()),'January')) OR (CONTAINS(DATENAME('month',TODAY()),'May'))
      OR (CONTAINS(DATENAME('month',TODAY()),'July')) OR (CONTAINS(DATENAME('month',TODAY()),'September'))
      OR (CONTAINS(DATENAME('month',TODAY()),'November')) OR (CONTAINS(DATENAME('month',TODAY()),'December'))

      THEN((DATEDIFF('day',[First Day (current month)],[Last Day (current month)]) -(7-DATEPART('weekday',[First Day (current month)])) -
      DATEPART('weekday',[Last Day (current month)])) / 7*5
      + MIN(5,(7-(DATEPART('weekday',[First Day (current month)]))))+ MIN(5,(DATEPART('weekday',[Last Day (current month)])-1))-1)

      ELSE
      (DATEDIFF('day',[First Day (current month)],[Last Day (current month)]) -(7-DATEPART('weekday',[First Day (current month)])) -
      DATEPART('weekday',[Last Day (current month)])) / 7*5
      + MIN(5,(7-(DATEPART('weekday',[First Day (current month)]))))+ MIN(5,(DATEPART('weekday',[Last Day (current month)])-1))

      END

       

      Now you can reference business days in any other calculations you have