Calculate Business day for each day of a month

[AnandKumar1]

Though i found many threads for Business day problem but nothing have helped me much.

I just want to find the business day for each of weekday in a month. Removing weekends.

I want something like this.

Date	Business day
1-Jul-20	1
1-Jul-20	1
1-Jul-20	1
2-Jul-20	2
10-Jul-20	8
15-Jul-20	11

And this will change for each month. Like for past/future months , the business day will be different. Not to worry about any holidays list.

Can someone help on this?

[DannyS]

@AnandKumar1 

I would check out this Community article below and take a look at the second solution that was marked as solved:

https://community.alteryx.com/t5/Alteryx-Designer/Calculate-Business-Days-in-Month-Removing-Holidays...

Hope this helps!

Best,

Danny

[AnandKumar1]

yeah i've tried that also but not getting correct output.

Like for 01-May-2020 i was getting 21 as business day but it should come as 1. Infact all the values were actually coming in reverse.

Can you use my sample data?

Date             Business day
1-Jul-20           1
1-Jul-20           1
1-Jul-20           1
2-Jul-20           2
10-Jul-20         8
15-Jul-20         11

[DannyS · Accepted solution]

@AnandKumar1 

I see the issue here. I've attached a workflow that should be a good solution for you. The only added thing you will need to do is generate a list of dates the span across the month of the data you are using. For example since you gave me a sample record set of Jul 1 - Jul 15, I went ahead and created a lookup table for the month of July. You then create your business days for the entire July dataset and Join in your sample data to get the desired result:

Hope this helps!

[ChiN]

Hi @AnandKumar1,

Does this work? See the attachment.

[AnandKumar1]

@DannyS 

Thanks for your solution.

But it would be better if we find some way to read month day automatically by alteryx instead of manually entering. Because every month i've to do this manual task and if by any chance i got to work for 2-3 month of data together then it will become really hectic to manually input 2-3month date.

[DannyS · Accepted solution]

@AnandKumar1 

Yes there are a couple of options for you. The easiest in my opinion would be to either find your starting date for your current population (e.g. 7/1/2020 in your previous example) and use a Generate Rows tool to increment the records by +1. From there the only change you would make to the previous workflow is make sure to group by the column MonthYear in your Multi-Row formula tool. I've attached a sample workflow that shows this in detail.

In my example, I started with the date 1/1/1970 and generated the rows for each date from 1/1/1970 up until today's calendar date using the DateTimeNow() function: 

This option will at least be less manual than the first version I attached, and what's good about this option is you could choose any start date you want and generatethe dates, that way you only have to create that record set once. If you want to get even more creative, I would suggest doing some research on iterative macros and see how you could implement one into your current flow.

Hope this helps

[AnandKumar1]

Thanks @DannyS for this.

This solution is best fit for my requirement.