Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to count the number of weekends/weekdays between two dates in Excel?

Sometimes, we need to know how many specific weekdays between two dates. For example, I have two dates: the start date is 1/1/2014 and the end date is 2/15/2014, now I want to know how many times a Sunday or a Monday or a Tuesday etc occurs in this duration. Maybe this is somewhat difficult for us, but here, I can talk about some effective methods for you.

Count the number of specific weekdays/weekends between two dates

Count the number of specific weekdays/weekends in a given month

Batch count the number of each weekday in a given month

Count numbers of all weekends/weekdays/specific day of week between two dates

Count the number of specific day of week (Monday, Saturday, Sunday, etc) between two dates in Excel


arrow 
blue right bubble Count the number of specific weekdays/weekends between two dates with formula

Supposing, I have the following two dates, and I need to count how many Sundays between them. Select a blank cell, enter this formula =INT((WEEKDAY($B$1- 1)-$B$1+$B2)/7) , and press the Enter key. And now you will get the number of Sundays between the two dates. See screenshot:

Notes:

(1.) In the above formula, B1 is the start date and B2 indicates the end date.

(2.) The red 1 stands for Sunday. In this formula, you can replace the number 1 with other numbers between 1 and 7. (1 is Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday and 7 is Saturday)


arrow 
blue right bubble Count the number of specific weekdays/weekends in a given month with User Defined Function

You can also create a User Defined Function to calculate how many specific weekdays in a given year and month besides the above formula.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

Public Function TotalDays(pYear As Integer, pMonth As 
Integer, pDay As Integer)
'Update 20140210
Dim xindex As Integer
Dim endDate As Integer
endDate = Day(DateSerial(pYear, pMonth + 1, 0))
For xindex = 1 To endDate
    If Weekday(DateSerial(pYear, pMonth, xindex)) = pDay Then
        TotalDays = TotalDays + 1
    End If
Next
End Function

3. Save this code and return to the worksheet, then in a blank cell, enter this formula = TotalDays(year, month, 1) . In this example, I will count how many Sundays there are in August 2015, so I can apply this formula as this = TotalDays(2015, 8, 1), then press Enter key, and you will get how many Sundays there are in August 2015. see screenshots:

Notes:

(1.) In the formula, 2014 is the year and 1 indicates the month, you can change them as your need.

(2.) Another red 1 stands for Sunday, you can replace the number 1 with other numbers which from 1 to 7 (1 is Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday and 7 is Saturday)


arrow 
blue right bubble Batch count the numbers of all weekdays in a given month with Kutools for Excel

This method will apply some utilities of Kutools for Excel to convert dates in a give month to weekday names,and then batch count these weekday names in the give month easily.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. List all dates in the give month in a column. In our case, the given month is January of 2014, so that we type the first date 2014/1/1 into the Cell A2, and then drag this cell's Fill handle down to 2014/1/31. See screenshot:

2. Keep selecting these dates, and click Kutools > Format > Apply Date Formatting; and then in the opening Apply Date Formatting dialog box, click to highlight Wed or Wednesday in the Date formatting box, and click the Ok button. See screenshot above:

3. Keep selecting the dates shown as weekday names, and then click Kutools > To Actual to convert dates to text.

4. Add a column right to original Dates column, and type the column name of Count. Select the Dates column and Count column, and then click Kutools > Content > Advanced Combine Rows.

5. In the opening Combine Rows Based on Column dialog box, click to highlight the Date column and then click the Primary Key; click to highlight the Count column and then click the Calculate > Count; click the Ok button.

And now you will see it has counted the numbers of each weekday and each weekend in the give month. See screenshot:

doc count weekday weekend 11

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


arrow blue right bubble Count numbers of all weekends/weekdays/specific day of week between two dates with Kutools for Excel

Actually, we can apply Kutools for Excel's Count Weekends between two dates formula, Count Weekdays between two dates formula, and Count the number of specific weekdays formula to quickly count the numbers of all weekends, weekends, or specific day of week in a date range in Excel.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

Count the number of weekends (Saturday and Sunday) between two dates in Excel

Select a blank cell, click Kutools > Formulas > Count Weekends between two dates to open the Formula Helper dialog box. And then specify the start date and end date into corresponding box, and click the Ok button. Now you will get the number of all weekends between these two dates. See screenshot:

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!

Count the number of weekdays (excluding Saturday and Sunday) between two dates in Excel

Select a blank cell, click Kutools > Formulas > Count Weekdays between two dates to open the Formula Helper dialog box. And then specify the start date and end date into corresponding box, and click the Ok button. Now you will get the number of all days excluding the weekends in the specified date range. See screenshot:

doc count weekday weekend 2 2

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!

Count the number of specific day of week (Monday, Saturday, Sunday, etc) between two dates in Excel

Select a blank cell, click Kutools > Formulas > Count the number of specific weekday to open the Formula Helper dialog box. Next specify the start date and end date into corresponding box, type a number (from 1 to 7) into the Weekday box, and click the Ok button. Now you will get the number of all days excluding the weekends in the specified date range. See screenshot:

Note: 1 indicates Sunday, 2-5 means Monday to Friday, and 7 is Saturday.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


arrow blue right bubbleDemo: count the number of weekends/weekdays between two dates in Excel

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mohamed · 1 years ago
    Let's say you want to calculate the 3rd Thursday of the month and assume the date of the first of the month is in cell A1.

    We need to first work out the date of the Thursday in the week of A1. Because Thursday is the 5th day of the week, we use:

    [b]=[u]A1-WEEKDAY(A1)+5[/u][/b]

    Then if this Thursday falls before A1, we need to add 7 using

    [b](([u]A1-WEEKDAY(A1)+5[/u])
  • To post as a guest, your comment is unpublished.
    Jon · 1 years ago
    What about a formula that returns the actual date of the first Wed of each month, Or the 2nd and 3rd tuesday of each month?
    Or every 3rd tuesday?

    I want to be able to put in my own start and end dates and then get the actual DATES (not the count) returned to me. Any ideas?
  • To post as a guest, your comment is unpublished.
    KAMBLE VIJAY · 2 years ago
    hi sir,
    i want to calculate no. of weeks in excel between two dates, but Dose not see right weeks as per date
    for example: I enterd the date 01/01/2016 ( Friday) and second date 14/01/2016( thursday) no of weeks showing = 2 weeks.
    but i want to show exact 2 weeks completed 15/01/2016 other wise show previse no fo weeks.
    • To post as a guest, your comment is unpublished.
      Mohamed · 2 years ago
      Let the start and end dates be in cells A1 and A2, respectively. This should work:

      =INT((A2-A1)/7)
  • To post as a guest, your comment is unpublished.
    Rob Mormile · 4 years ago
    Is it possible to have the start date set to "=today()" and the end date, for example, the 22nd of the current cycle. As the date returns to the 23rd, refresh the formula to the following 22nd?
    • To post as a guest, your comment is unpublished.
      Mohamed · 2 years ago
      Assuming cycle refers to month, this should work for the end date:

      DATE(YEAR(TODAY()),MONTH(TODAY())+IF(DAY(TODAY())>22,1,0),22)
  • To post as a guest, your comment is unpublished.
    Mohamed · 4 years ago
    Why are my comments not published completely????
    • To post as a guest, your comment is unpublished.
      Admin_jay · 4 years ago
      [quote name="Mohamed"]Why are my comments not published completely????[/quote]
      Sorry, please try to send me the formula to jaychivo#extendoffice.com. Please replace @ with #.

      And i will help you post it. May be there are some characters which have been blocked. :-)