2 Replies Latest reply on Oct 14, 2015 8:42 AM by brenda.kimiywi

    Help with calculating # of business days

    brenda.kimiywi

      Hello, I am trying to calculate the number of business days between the 1st of the month and today's date for the current month Oct 15, previous month Sep 15 & current month prior year Oct 14. The first two (Oct 15 and Sep 15) are fine but the prior year is returning zero. See below. Help!

       

      if YEAR([Rpt Date]) = YEAR( TODAY()) AND MONTH([Rpt Date]) = MONTH(TODAY())

              then DATEDIFF("weekday", DATETRUNC('month', TODAY()), TODAY())-2 *

                  (DATEPART('week', TODAY()) - DATEPART('week', DATETRUNC('month', TODAY())))

       

           elseif YEAR([Rpt Date]) = YEAR( TODAY()) AND MONTH([Rpt Date]) = MONTH(DATEADD('month', -1, TODAY()))

              then DATEDIFF("weekday", DATETRUNC('month', DATEADD('month', -1,TODAY())), DATEADD('month', -1, TODAY()))-2 *

                  (DATEPART('week', dateadd('month', -1, TODAY())) - DATEPART('week', DATETRUNC('month', dateadd('month', -1, TODAY()))))

       

           elseif YEAR([Rpt Date]) = YEAR( TODAY()-1) AND MONTH([Rpt Date]) = MONTH(TODAY())

              then DATEDIFF("weekday", DATETRUNC('month', DATEADD('year', -1,TODAY())), DATEADD('year', -1, TODAY()))-2 *

                  (DATEPART('week', dateadd('year', -1, TODAY())) - DATEPART('week', DATETRUNC('month', dateadd('year', -1, TODAY()))))

       

      ELSE 0

      END