1 Reply Latest reply on Sep 28, 2018 9:43 AM by swaroop.gantela

    DATEDIFF not returning correct total for number selling days in a full month

    Geneth McAlister



      I am trying to calculate the number of selling days in a full month so that I can do a projection calculation. The calculation to calculate the number of selling days to date is correct (so far anyway).

      my calculation is:

          DATEDIFF("weekday", DATETRUNC('month',[End Date Parameter]), DATEADD('second',-1,DATEADD('month',1,DATETRUNC('month',[End Date Parameter]))))

      - 2 * (DATEPART('week', DATEADD('second',-1,DATEADD('month',1,DATETRUNC('month',[End Date Parameter])))) - DATEPART('week', DATETRUNC('month',[End Date Parameter])))

      + (IF DATENAME('weekday',DATEADD('second',-1,DATEADD('month',1,DATETRUNC('month',[End Date Parameter])))) = 'Saturday' OR DATENAME('weekday',DATETRUNC('month',[End Date Parameter])) = 'Sunday' THEN 0 ELSE 1 END)


      Alternatively is there a better way to calculate the number of working days between 2 dates? I need to calculate this for the month as well as the financial year. The Financial year start date is driven off a parameter as is the Report end date.