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

# Calculation for working days

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?

• ###### 1. Re: Calculation for working days

Raul,

Here you go.

You can learn detail logic in this link.

Excel's Networkdays Alternative (Count days excluding Weekend) [version 2]

Thanks,

Shin

• ###### 2. Re: Calculation for working days

Thanks Shinichiro, I already went this way but as I mentioned before, it does not allow me to manipulate holidays and such in here at least I create another equation.

My goal is to figure out how to get the table connect with the main database so it produces a lookup for today and give me days in a month and day of the month where i can control weekends and holidays

I was able to do it with a parameter but we need this to be automated so every night at 1:00 am the report runs itself and emails itself without human interaction.

• ###### 3. Re: Calculation for working days

May be this?

Thanks,

Shin