Difference Between 2 Dates, Without the Weekends

It has always been possible in Microsoft Excel to find the difference between 2 dates. And there are hordes of other date and time functions in Excel, some of which I wrote earlier about.

Today, let us look at a recently added function in Microsoft Excel 2010, which can help you find the differenc in between any 2 dates, and this function works beautifully, even if your weekend does not fall on a Saturday/Sunday.

For example, recently I was in Cairo, Egypt to conduct a Corporate Training, and found out that their weekends fall on Friday and Saturday.

The regular function that I used earlier – NetworkDays() –  is good  at finding the days between any 2 dates, but it assumes that the weekend is on Saturday/Sunday.

Fortunately, Microsoft has added an International Function in Excel, which helps to work this out andmakes it fairly easy.

The function is NETWORKDAYS.INTL().

It takes 4 arguments – Start_date, End_date, Weekend & Holidays. The last 2 are optional, but that is where the function shines and differs from the Networkdays() function.

The weekends argument can take one of these several values, and when you type the formula, this helpful guide pops-up.

Weekends in Microsoft Excel

As you can see, you can easily select any single day, or a two consecutive days as your weekends.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


For Egypt, Dubai, their weekend of Friday, Saturday has the value of 7.

Thus, I choose the following function:

=NETWORKDAYS.INTL(“1-Sep-2012”, “31-Sep-2012”,7)

and promptly got the result as 21, which is the number of working days in this period, excluding the Fridays & Saturdays.

If you only use =NETWORKDAYS(“1-Sep-2012”, “31-Sep-2012”)

you will get the answer as 20 days, which is based on the Saturday/Sunday weekends.

So there you are. Have your cake and eat it too! You can check out these function in the difference-between-dates Excel file, which you can use to practice and test out these functions.

Anything else you’d like covered here? Do post a comment and let me know.

Hope you enjoyed this Excel Tip. If you would like some more tips, simply subscribe to the ExcelChamp Tips Newsletter here.

Cheers,
Vinai Prakash
Founder of ExcelChamp.Net – A community for Excel Tips & Tricks

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!