{"id":61,"date":"2012-09-17T00:27:31","date_gmt":"2012-09-16T16:27:31","guid":{"rendered":"http:\/\/excelchamp.net\/?p=61"},"modified":"2022-07-11T06:27:43","modified_gmt":"2022-07-10T22:27:43","slug":"difference-between-2-dates-without-the-weekends","status":"publish","type":"post","link":"https:\/\/www.excelchamp.net\/difference-between-2-dates-without-the-weekends\/","title":{"rendered":"Difference Between 2 Dates, Without the Weekends"},"content":{"rendered":"

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<\/a>, some of which I wrote earlier about.<\/p>\n

Today, let us look at a recently added function in Microsoft Excel<\/strong>, which can help you find the difference between any 2 dates, and this function works beautifully, even if your weekend does not fall on a Saturday\/Sunday.<\/em><\/p>\n

For example, recently I was in Cairo, Egypt to conduct Corporate Training, and found out that their weekends fall on Friday and Saturday.<\/strong><\/p>\n

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

Fortunately, Microsoft has added an International Function in Excel, which helps to work this out and makes it fairly easy.<\/strong><\/p>\n

The function is NETWORKDAYS.INTL().<\/strong><\/p>\n

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

The weekend argument can take one of these several values, and when you type the formula, this helpful guide pops up.<\/p>\n

\"Weekends<\/a><\/p>\n

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

\n\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Want to Improve Your Excel Skills?<\/strong><\/span><\/p>

Learn the Key Features of Excel Quickly & Easily, by <\/span>Joining the
Online Training<\/strong> on <\/u>Basic \/ Intermediate Excel<\/u>.<\/strong><\/a><\/span><\/p>

To Get Most out of Excel, Learn the\u00a0 Pivot Table techniques in our
Pivot Table Masterclass Training<\/a><\/u><\/b>!<\/strong><\/a><\/span><\/p><\/div>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t
\nFor Egypt and Dubai, their weekend of Friday, and Saturday has the value of 7.<\/strong><\/p>\n

Thus, I choose the following function:<\/p>\n

=NETWORKDAYS.INTL(“1-Sep-2012”, “31-Sep-2012”,7)<\/strong><\/p>\n

and promptly got the result as 21, which is the number of working days in this period, excluding Fridays & Saturdays.<\/p>\n

If you only use =NETWORKDAYS(“1-Sep-2012”, “31-Sep-2012”)<\/strong><\/p>\n

you will get the answer as 20 days, which is based on the Saturday\/Sunday weekends.<\/p>\n

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

Anything else you’d like covered here? Do post a comment and let me know.<\/p>\n

Hope you enjoyed this Excel Tip. If you would like some more tips, simply subscribe to the ExcelChamp Tips Newsletter here<\/a>.<\/p>\n

Cheers,
\nVinai Prakash
\nFounder of ExcelChamp.Net – A community for Excel Tips & Tricks
\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Are you facing any problem in using Excel? Any Question?<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

You have come to the right place<\/span>. Tell us your needs. We’ll be glad to help you!<\/span><\/strong><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\n\t\t\t\n\t\t\t\n\n\t\t\t\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t