Today we are going to discuss about unknown gem of hack in date functions,
WORKDAY.INTL and NETWORKDAYS.INTL.
Let me give you a glimpse of our requirement,
WORKDAY.INTL :
We have start date of project, no of days, and in what days you will be working on project. Our requirement is to find end date.
We have start date and end date of project, in what days you will be working in project. Our requirement is to find the number of days required to complete the project.
Tricky part in both requirements is that, working days are not continuous days from Monday to Friday. This can be done in single cell formula without any assistance from helper cells.
Lot to learn, let's dive into exciting stuffs.
For our first requirement, formula in cell C7 will look like below,
=WORKDAY.INTL(C4,C5,"0100111",$E$5:$E$7)
The argument of this function is,
=WORKDAY.INTL(start_date,days,[weekend],[holidays])
You can see the square brackets in last 2 arguments, which means these arguments are optional.
For start date and no of days, we have inputs in C4 and C5 cells respectively.
Drop down option is there to accommodate all combinations of continuous weekends either for 2 days or for 1 day.
But in our case, weekends are Tuesday, Friday, Saturday, Sunday. For us, the weekend seems to be confusing part and disrupts the further proceedings. But for excel it is like a piece of cake. I will explain you about the flexibility of weekend argument,
We can select any of the options in drop down or we can input text in organized format.
Since, this is the weekend argument, non-working days will be 1 and working days will be 0 and start of the week is Monday.
So, in our case,
is Monday weekend? No, then start with 0.
is Tuesday weekend? Yes, then start with 1.
is Wednesday weekend? No, then start with 0.
is Thursday weekend? No, then start with 0.
is Friday weekend? Yes, then start with 1.
is Saturday weekend? Yes, then start with 1.
is Sunday weekend? Yes, then start with 1.
Our text format of weekend will look like this "0100111".
Then holidays argument helps us to make sure that local holidays are excluded in project days. This will exclude, if any of our workday falls into holiday. Here 01/05/2024 is holiday which is Wednesday and so this will not be considered as workday in our calculation.
For our 2nd requirement, formula in cell C14 will look like below,
=NETWORKDAYS.INTL(C11,C12,"1011011",$E$12:$E$14)
This is straight forward, start date and End date is provided. As discussed in above case, here also weekend is bit tricky and what we have to find is number of days.
Arguments of this function is,
=NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])
We have all our requirements as inputs, so no need to need to explain again.
留言