Networkingdays() an improved version of networkdays formula

Networkdays - Calculate working days between 2 given dates in excel

We all know that networkdays() an extremely powerful and simple excel formula can help you calculate no. of working days between 2 given dates.

But there is one problem with it. It assumes 5 day workweek starting with Monday to Friday. Not all countries have workweek from Monday to Friday. As Incubus wrote to me in an e-mail,

In Excel, the function “networkdays” doesn’t work for users in the middle east ‘cos it counts Saturday & Sunday as weekend. This is good enough only if you live in elsewhere, but for us weekend is Friday & Saturday.

This got me thinking and I ended up writing a user defined formula (UDF) to calculate working days between 2 given dates with any criteria. This will be good for calculating payrolls for temporary workers, offshore partners and of course people working countries where Saturday or Sunday or not usually holidays.

Please download the NetWorkingDays Add-in if you want to use this function.

If you are curious what is inside, see the UDF code

How to use the NetWorkingDays() UDF?

Once you download the add-in, just install the add-in by,

[in Excel 2003] By going to Tools > Addins > Browse
[in Excel 2007] By going to Office Button > Excel Options > Addins > “Go button” > Browse
Specify the location where you saved the downloaded file

Now that the add-in is installed, you can use the UDF by writing a formula like this:

=networkingdays("01-06-2009","02-07-2019","12356")

The first argument is start date, the second one is end date and third one tells which days of week are working (Monday is 1 and Sunday is 7). So the above formula counts all the Mondays, Tuesdays, Wednesdays, Fridays and Saturdays between 01-06-2009 and 02-07-2019

Known Errors and Limitations

The formula returns #VALUE! error when you try really long durations (like trying to find all the workingdays between now and a century later)

Another thing is, you cannot feed a custom holiday list to this formula. But that is very easy to add on, so I didn’t bother.

So go ahead and give it a try

Download the NetWorkingDays() UDF Excel Add-in

and tell me what you think…

On dates and times in Excel: Date & Time Formulas | 10 Tips on Using Dates & Times in Excel

| More

Posts & Navigation

Tags: date and time, downloads, free, Learn Excel, macros, Microsoft Excel Formulas, MS, networkdays(), networkingdays(), spreadsheets, udf, user defined functions, VBA

Trackbacks & Pingbacks

Pingback by How to Calculate Elapsed Time using Excel Formulas? | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on September 22, 2009 @ 9:11 am

Pingback by How to Calculate Elapsed Time using Excel Formulas? | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on September 22, 2009 @ 9:11 am

Comments

chrisbrooke June 19, 2009

Hi – It appears that to display the number of Mondays, Tuesdays and Wednesdays for example between two dates, using the string “123″ as per the example, the first date has to be a Monday. Otherwise, for example, if the start date is a Thursday, the use of “123″ returns the number of Thurday, Friday and Saturdays between the dates.

Chandoo June 22, 2009

@Chrisbrooke: Thanks for your comments. I remember specifically testing for this scenario. I will once again test this and post a fix in the coming days. Thanks for telling me about this.

Glenn August 4, 2009

I have a date with time stamps. I want to take a time 8/1/09 12:00am and convert to 7/31/09. Any time after 12am until 7am I want to converrt back to previous day. Can you help?

Tanvir August 25, 2009

In our country working days are Sunday to Wednesday.
Can you please provide me the UDF formula to calculate the working days between two date from two differnt cell.

Jason October 13, 2009

Chandoo – Please show me how you would add the holiday list functionality to your UDF. Thanks for your reply!

Chandoo October 16, 2009

@Jason: Thanks for the question,

Here is the basic algorithm.

loop through the list of holidays
if the holiday is between start and end days and if the holiday’s day of week is a working day
decrease the return value by 1
do for next holiday

you can add it towards the end of the function.

If you do write it, share the code through comments so that others can also benefit from the same. All the best.

RSS feed for comments on this post. TrackBack URI