3 Replies Latest reply on Jun 15, 2015 6:33 PM by Nicholas Hannan

    How to create a formula for X out of X business days?


      Hi everyone,


      I have a company dashboard that looks like this (marks removed for privacy):


      Right underneath the date, I want it to say how many business days we are into the month, out of how many total business days exist for the month.


      I can calculate how many business days we have had so far with this:


      If [Day Of Week]=2 or [Day Of Week]=3 or [Day Of Week]=4 or [Day Of Week]=5 or [Day Of Week]=6

      //If it's Monday-Friday

      then [Day Of Month] end


      The problem is, I can't get the list of how many business days exist in the month altogether because the [Date] only counts how many days have occurred so far.


      So how do I calculate how many total business days there are this month when this month isn't over?


      I tried something like this:


      DATEDIFF('day', DATETRUNC('month',today()),DATETRUNC('month',DATEADD('month',1, today() )))


      This SHOULD have said "How many days are there between the Datetrunc of this month versus the Datetrunc of last month" because ideally it should have given me the total number of days this month, but for some reason the answer comes out as only 1.


      Thank you in advance for your help