Bhavik’s Formula

Bhavik’s Formula is based around the Excel Networkdays( ) function, which is wrapped inside a Max ( ) function and uses a couple of other Min( ), Max( ) and Eomonth( ) functions as part of the Networkdays( ) parameters.

=MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,0))))

Lets jump in and see what makes Bhavik’s Formula tick.

=MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,0))))

Caution

Instead of using the cell Ranges in describing the functionality of the file, I am going to use the following words

Join Date: The Date that the employee Joined the company, Column A

Finish Date: The Date that the employee’s position ended with the company, Column B

Month: The current Month’s Date. The month’s are described as dates using the first of the month as a Reference date. Eg: April 2012 = 1/4/2012

So to rewrite Bhavik’s Formula we using the above names (These aren’t Named Formulas)

We can see that Bhavik’s Formula hasn’t included the optional Holidays functionality of the Networkdays( ) function. This will be discussed later.

Networkdays returns the number of work days between the Start Date and the End Date.

Start Date

In Bhavik’s Formula, the Start Date is defined as MAX(Join Date, Month)

This will select the later date or highest of the Join Date and the Current Month. Hence if the Join Date is before the current month, the Current Month will be used as the start date.

If the Join Date is during the Current Month, the Join Date will be used as it will be higher than the Current month.

If the Join Date is after the Current Month, Networkdays will return a negative. This is dealt with by the leading Max(0, ) function which will take a 0 value if Networkdays returns a negative number as any negative number is less than zero.

If the Finish Date is during the current month, the Finish Date will be used as the End Date.

If the Finish Date is after the current month, the End of Month of the Current Month will be used as the End Date.

Application

Now we understand how the formula works we can have a look at it in use :

In cell C2 put:

=MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,0))))

Copy across and down

There is an example of every date combination described above and listed in the Options Column.

Now that you understand the logic, You can work through these cells, one by one, and examine why each month’s formula works.

Holidays

In the syntax of the Networkdays( ) function, you will see there is an optional Holidays parameter.

This can be as simple as a range of cells or an array of dates defining the holidays

In the worked example the range B13:B15 contains 3 dates reflecting 3 Public Holidays in Australia.

When the dates are added to the formula

=MAX(0,NETWORKDAYS(MAX($A9,C$1),MIN($B9,EOMONTH(C$1,0)),$B$13:$B$15))

We can see that the workdays in January and March are reduced by 1 day each, noting that New Years Day is on a Sunday and hence not included as a Holiday.

I should note that in Australia the New Years Day holiday is actually taken on the following Monday (2 Jan 2012), but this was excluded for this example as a demonstration only to show that because it is on a Sunday it is not included.

New Functions !

In Excel 2010, Microsoft introduced the Networkdays.intl( ) function.

This is a new version of the Networkdays Functionality but has the added benefit of being able to define the Weekends.

The Excel Networkdays.intl( ) function uses the following syntax:

The main benefit of using the Excel Networkdays.intl( ) function is that you can define your own weekends, rather than rely on the standard Saturday/Sunday option that Networkdays( ) provides.

The weekend parameter is set to Z1 and so Excel will retrieve the value from cell Z1 to define the weekend. Cell Z1 must contain a valid number from 1 to 17 as described above or a 7 character Text string like ‘1010100.

I strongly recommend that users who have switched to Excel 2010 start using the new formulas, as they add a raft of new features to your Excel arsenal.

Formula Forensics “The Series”

Formula Forensics Needs Your Help !

I need more ideas for future Formula Forensics posts and so I continue to need your help.

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post like above or;

If you have a formula that you would like explained but don’t want to write a post as Bhavik’s has done here, send it to Hui or Chandoo.

Sign-up for our FREE Excel tips newsletter:

Here is a smart way to become awesome in Excel. Just signup for my FREE Excel tips newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:

I had someone recently ask for number of working days between two dates by weekday.

Here’s what I came up with; I would be interested to see if anyone has a different solution:

[Uses two system tables, Semesters and Holidays]

[Semesters has 10 columns:

Semester – The name of the date range (obviously a school semester in my example, but not necessarily so.)start – the first date in the date rangeend – the last date in the date rangeSunday – shows the number of Sundays in the date range, excluding Sunday holidaysMonday – you get the idea.TuesdayWednesdayThursdayFridaySaturday]

I am trying to use the following (copied it from this post). =MAX(0,NETWORKDAYS.INTL(MAX($B21,D$20),MIN($C21,EOMONTH(D$20,0)),1,$C$32:$C$34))
What need to do is has the data calculate to a status date vs. going on if there is no Finish date.
I have been working on it fro 2 days now and need some help. Can anyone help me please.
Thank you in advance for any and all help…

I am attempting to make a calendar that will allow me to enter a date and then it will calculate for me the date that is 60 days before that date, 45 days before the date entered, 60 days after the date entered, and 120 days after the date entered. And it needs to exclude weekends and federal holidays for some of these time periods. Any ideas?