This month’s Financial Function tip is on the NETWORKDAYS.INTL function which is very similar to the NETWORKDAYS function that we covered last month. Both functions are used to calculate the number of working days between two dates. The difference is that NETWORKDAYS limits weekend days to Saturday and Sunday, NETWORKDAYS.INTL allows you to choose any two days of the week for the weekend or even set only one day per week if that’s most applicable. Another difference is that NETWORKDAYS.INTL is only in Excel 2010 while NETWORKDAYS has been around for quite a few Excel versions.
The video tutorial below will walk you through using the NETWORKDAYS.INTL Function (a full transcript of the video is below):
You can download the workbook to practice here.
Get your Free copy of our Financial Functions Quick Reference Guide:Click Here
NETWORKDAYS.INTL Function Tip Video Transcript:
Microsoft Excel uses numbers to represent dates. By using the numbers method, it’s very easy to calculate the difference between two specific date values. We can use this to calculate the number of days between any given period, however if we want to calculate the number of business days, we can use the NETWORKDAYS function.
The NETWORKDAYS function can take a start date and end date however it makes the basic assumption that Saturdays and Sundays will always be non-trading days. This may not work for a lot of businesses, they may have Sunday trading, they may have Saturday trading, they may only observe that Friday is a day of closure for the business. That’s where the NETWORKDAYS.INTL function steps in.
Very similar to the NETWORKDAYS function, you can give it a start date and an end date as well as either one or a range of holiday dates, and it will calculate the number of days given that combination. However the NETWORKDAYS.INTL function takes it a step further, with a weekend option. You can see with this particular weekend option that we can choose sequential days of Saturday, Sunday; Thursday, Friday, etc. or by using a specific number value, select, only 1 day of the week as a non-trading day. In addition we can actually provide a string of numbers using 0s and 1s to represent days of closure. 0s represents that this is a trading day, 1 represents this is the day of the week that the business will not be open. For example 0010100 would mean that Monday and Tuesday are trading days, 1 on Wednesday and 1 in the Friday position means that those 2 days would not be counted as business days for the date range.
Looking at this particular spreadsheet that we have set up, we can see we have a start date of the 1st of March 2012 and an end date, the 30th of April 2012. Weekend code option is available; here we’ve selected the number 1 as a standard Saturday Sunday non- trading combination.
A list of public holidays has been set up in cells G3 through to G6, so let’s use the NETWORKDAYS.INTL function to calculate the number of business days given these parameter values. Our start date, our end date and our weekend options are already available and I can select a range of cells that represent my holiday values. With this combination I can see that I have 39 business days from the 1st of March to the end of April, taking into account Human Rights Day, Easter as well as Freedom Day.
However if I wish to change that to say, Monday and Tuesday are non-trading days, it’s a simple matter for me to change the weekend code value to 3. Mondays and Tuesdays are no longer represented as business days. You’ll see that Family Day falls on a Monday, hence it’s already removed because it’s already represented in our holiday’s value, but if we wanted non-consecutive days represented as non-trading days we could do that via the weekend string. 0010100 would mean that Wednesday and Friday should be calculated as non-trading days.
Let’s update our NETWORKDAYS formula to now represent the string value rather than the weekend code value. There are now 43 work days in that given period based on Wednesdays and Fridays no longer being represented as trading days.
Get your Excel Financial Functions Quick Reference Guide Now:
This Quick Reference Guide is a curation of the Top 15 most commonly used Excel Financial Functions.
