Count working days

[munchkin100]

Hi! I have two sets of data: 

- "Records", which has the dates, when a request has been raised and when completed

- "BankHol and Weekend", which has the list of bank holidays and weekends

I would like to count, how many days did it take to complete the task. I would like to exclude the weekends and bank holidays.

I have tried to use a Join tool: L for "Records" and R for "BankHol and Weekend". I thought about using Left output, but then I realized that it would exclude any tasks raised at the weekend or bank holiday.  I cant use True/False for weekends either, as again I dont want to exclude tasks raised at the weekend, but I would like to exclude any bank holidays or weekend from the Open Days calculation.  So, if something was raised at 1/11 (Sun) and completed 17/11(Tue), then it should say that it took 12 days to complete the task.

I would appreciate any help.

[AngelosPachis]

Hi @munchkin100 ,

I've used  a generate rows tool to create all days between that of a task has started and that which finished. Then, with a Join tool, I join on the holidays-weekends dataset and whatever falls out of my R Join anchor are the dates that I need to count.

Hope that helps, let me know if you have any questions.

Cheers,

Angelos

[atcodedog05 · Accepted solution]

Hi @munchkin100 

Here is a workflow for the task.

Output:

Workflow:

1. generate days between start and complete

2. remove holidays

3. summarize and count

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

Edit : We both posted so close few seconds difference😅

[Buzz]

HI @munchkin100 ,

you an also use the python functionality. There is a dedicated function for it. The +1 in "df['Working Days'] = np.busday_count(A, B,holidays=holidays)+1" is added for having the Start Date/End Date also counted as a working day.

   

[munchkin100]

@Buzz , @atcodedog05 , @AngelosPachis  Thank you very much for your replies. All the solutions are brilliant. Shame I could highlight only one. I would mark all of them as a solution, if I could. Much appreciated! you guys saved my life!

[atcodedog05]

Hi @munchkin100 

As a matter of fact you are allowed to mark multiple post as solution. Hence feel free🙂

Happy to help 🙂

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

[AngelosPachis]

Glad we could help @munchkin100 ,

To be fair, I think you can highlight/accept more than one solutions.

[CarolAnn]

this is really great, but do you know how I can run it without having to change (remove) the "+1"?  I have a situation where a holiday lands on the end date and it does not calculate correctly unless I remove the "+1".

[CarolAnn]

found this, I will try it:

Important to remember is that slicing with labels using the loc method expects inclusive ends, whereas slicing using labels using the iloc method expects exclusive ends.