Excel - Count Days Between Two Dates Excluding Saturday - Excel - Episode 1354

2,223 views · Published 4 March 2011 · 5:55 · Indexed 31 October 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: Counting The Number of Days Between Two Dates - not including Saturdays.

Welcome back to another exciting episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head to head to solve your Excel problems. In this episode, we have a special treat as we are live in the same room and I have just brought Mike the first box of our new book. Most publishers would just use UPS, but we wanted to make it more exciting for you.

The problem we are tackling today was sent in by Imran on Facebook. He needs to count the number of days between two dates, excluding Saturdays. Now, the usual formula for calculating the number of days between two dates is to simply subtract the earlier date from the later date and add one. However, in this case, we need to exclude Saturdays from the count. This is where things get a little tricky.

My solution involves using the NETWORKDAYS.INTL function, which is available in Excel 2010 and later versions. This function allows us to specify which days of the week should be considered as weekends. By setting Saturday as the only weekend day, we can easily get the number of days between two dates, excluding Saturdays. But, there's a catch. We also need to count the number of Saturdays between the two dates. To do this, I use a simple subtraction formula to subtract the number of days between the two dates (including Saturdays) from the number of days between the two dates (excluding Saturdays).

Mike, on the other hand, has a different approach using the WEEKDAY and ROW functions. He creates an array of dates between the two given dates and then uses the WEEKDAY function to determine which days are Saturdays. He then uses the SUMPRODUCT function to count the number of Saturdays in the array. This is a great solution, but it requires the use of array formulas, which can be a bit tricky for some users. However, Mike's solution also highlights the power of teamwork as we were able to combine our approaches to come up with an even better solution.

So, whether you prefer my straightforward solution using the NETWORKDAYS.INTL function or Mike's more advanced approach using the WEEKDAY and ROW functions, we hope you found this episode helpful. Don't forget to like, comment, and subscribe to our channel for more exciting Excel tips and tricks. And as always, keep on Excelling!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction and book announcement
(00:12) Question from Imran about counting days between two dates
(00:30) Formula for counting days between two dates
(01:06) Formula for counting Saturdays between two dates
(01:44) Mike's solution using the NETWORKDAYS.INTL function in Excel 2010
(02:07) Mike's solution using the WEEKDAY and INDIRECT functions
(04:23) Explanation of using SUMPRODUCT instead of SUM for arrays
(05:45) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Array formula
Counting days between two dates
Counting Saturdays
Indirect function
Networkdays.Intl function
Not counting Saturdays
Row function
Sum product function
Weekday function


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153034/

The Duel becomes a Team Effort today as Mike and Bill work with a variety of Functions.

More from this channel