3 Replies Latest reply on Jun 16, 2017 9:23 AM by Shinichiro Murakami

    Calculation for working days

    Raul Malaver

      Hello everyone

       

      I would like to know if there is a way to create an equation that can give me the days in a month and a control set of day in the month, for example January has 31 days but only 21 working days plus holidays.

       

      I have created this equation that uses today-1 to give me yesterday's report but it takes too much time to calculate.

       

      if month(today()-1) = 1  and DAY(today()-1) = 3  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 4  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 5  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 6  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 7  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 8  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 9  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 10  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 11  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 12  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 13  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 14  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 15  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 16  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 17  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 18  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 19  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 20  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 21  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 22  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 23  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 24  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 25  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 26  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 27  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 28  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 29  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 30  then 21

      elseif month(today()-1) = 1  and DAY(today()-1) = 31  then 21

      elseif month(today()-1) = 2  and DAY(today()-1) = 1  then 20

       

      I have the same calculation to provide me with Day of the month

       

      if month(today()-1) = 1  and DAY(today()-1) = 3  then 1

      elseif month(today()-1) = 1  and DAY(today()-1) = 4  then 2

      elseif month(today()-1) = 1  and DAY(today()-1) = 5  then 3

      elseif month(today()-1) = 1  and DAY(today()-1) = 6  then 4

      elseif month(today()-1) = 1  and DAY(today()-1) = 7  then 4

      elseif month(today()-1) = 1  and DAY(today()-1) = 8  then 4

      elseif month(today()-1) = 1  and DAY(today()-1) = 9  then 5

      elseif month(today()-1) = 1  and DAY(today()-1) = 10  then 6

      elseif month(today()-1) = 1  and DAY(today()-1) = 11  then 7

      elseif month(today()-1) = 1  and DAY(today()-1) = 12  then 8

      elseif month(today()-1) = 1  and DAY(today()-1) = 13  then 9

      elseif month(today()-1) = 1  and DAY(today()-1) = 14  then 9

      elseif month(today()-1) = 1  and DAY(today()-1) = 15  then 9

       

      I created an excel table to provide this and use data as the relationship but I get this error

       

      Error: Cannot mix aggregate and non-aggregate arguments with this function, As i drop the measure of day of the month, it automatically sums it, any suggestions?