Count working days

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.

Question.xlsx

Datasets

Date Time

Expression

Data Investigation

Accepted answers

atcodedog05

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😅

days count'.yxmd

All comments

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.

Screenshot 2020-11-26 131404.jpg

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

Cheers,

Angelos

example.yxzp

atcodedog05

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😅

days count'.yxmd

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.

business-days-calculation.yxmd

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!

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703