Count working days
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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😅
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Glad we could help @munchkin100 ,
To be fair, I think you can highlight/accept more than one solutions.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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".
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
