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

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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.

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 tools for Excel. Full feature free trial 30-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.

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 tools for Excel. Full feature free trial 30-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 tools for Excel. Full feature free trial 30-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 tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


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

Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
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 · 2 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 · 2 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 · 3 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 · 3 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 · 3 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 · 5 years ago
    Why are my comments not published completely????
    • To post as a guest, your comment is unpublished.
      Admin_jay · 5 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. :-)
  • To post as a guest, your comment is unpublished.
    Mohamed · 5 years ago
    Hi Jaco,
    You may achieve this for someone who works Mondays (2), Wednesdays (4) and Fridays (6) as follows:

    (1) Call the year's start and end dates [quote]StartDate[/quote] and [quote]EndDate[/quote], respectively.
    (2) List all the public holidays in South Africa (this could span more than one year) in a range and call it [quote]PublicHolidays[/quote]
    (3) To calculate the total number of days worked enter the following [b]array[/b] formula:
    [quote]=INT((WEEKDAY(StartDate-2)-StartDate+EndDate )/7)+INT((WEEKDAY(StartDate-4)-StartDate+EndDate )/7)+INT((WEEKDAY(StartDate-6)-StartDate+EndDate )/7)-SUM(IF((PublicHolidays>=StartDate)*(PublicHolidays
    • To post as a guest, your comment is unpublished.
      Mohamed · 5 years ago
      Sorry the formula above is not complete: It should be:

      =INT((WEEKDAY(StartDate-[b]2[/b])-StartDate+EndDate)/7)+INT((WEEKDAY(StartDate-[b]4[/b])-StartDate+EndDate)/7)+INT((WEEKDAY(StartDate-[b]6[/b])-StartDate+EndDate)/7)-SUM(IF((PublicHolidays>=StartDate)*(PublicHolidays
    • To post as a guest, your comment is unpublished.
      Mohamed · 5 years ago
      I don't know what happened to my formula and the rest of my message above. The formula should be:

      =INT((WEEKDAY(StartDate-2)-StartDate+EndDate )/7)+INT((WEEKDAY(StartDate-4)-StartDate+EndDate)/7)+INT((WEEKDAY(StartDate-6)-StartDate+EndDate )/7)-SUM(IF((PublicHolidays>=StartDate)*(PublicHolidays
  • To post as a guest, your comment is unpublished.
    Jaco · 5 years ago
    Hi, I am from South Africa and I need advice.
    I have two workers at work who works different days. Now I want to type in a formula in excel to count how many days a year she work (that I can do), but the trick comes in when I want to type in a formula which allows me to deduct if one of her working days is a public holiday for example she works Monday, Wednesday and Friday. That means she works 156 days per year, but I want excel to deduct the holidays if it is on one of her working days. Can someone please assist me?
  • To post as a guest, your comment is unpublished.
    Laura Tigers · 5 years ago
    Does not work properly. You need to consider what day you're starting from and ending with!
  • To post as a guest, your comment is unpublished.
    PhilT · 5 years ago
    Thank you for this.
    Question, how do I add another day like "Wednesday or 4" to the Monday? Basically I want it to calculate both the total of Mondays and Wednesdays between the two dates. How do I write this formula?
    Thanks again
    • To post as a guest, your comment is unpublished.
      JamesB · 4 years ago
      To help future seekers.
      Use this formula for calculating days between two dates:
      =NETWORKDAYS.INTL( start_date, end_date, [weekend], [holidays] )
      =NETWORKDAYS.INTL(A3,A4,"00000011",C3:C8) - 0=include day 1=exclude day
    • To post as a guest, your comment is unpublished.
      Mohamed · 5 years ago
      Try
      =INT((WEEKDAY($B$1-2)-$B$1+$B2)/7)+INT((WEEKDAY($B$1-4)-$B$1+$B2)/7)