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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920