Note: The other languages of the website are Google-translated. Back to English

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 with formula

Supposing, I have the following two dates, and I need to count how many Sundays between them. Select a blank cell, enter below formula, and press the Enter key. And now you will get the number of Sundays between the two dates. See screenshot:

=INT((WEEKDAY($C$2- 1)-$C$2+$C3)/7)

Notes:

(1) In the above formula, C2 is the start date and C3 indicates the end date.

(2) In above formula, 1 stands for Sunday. And 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)


Use formula to count the number of specific weekday in month

Sometimes, you may need to count the total number of a certain weekday in the given month, says count the total number of Wednesday in July, 2020. Here, I will introduce a formula to count the total number of a certain weekday in the specified month easily.

Select a blank cell, type below formula, and press the Enter key to get the counting result.

=INT((WEEKDAY(DATE(G2,G3,1)- G4)-DATE(G2,G3,1)+EOMONTH(DATE(G2,G3,1),0))/7)

Notes:

(1) In above formula, G2 is the specified year, G3 is the specified month, and G4 is the specified day of week.

(2) This formula assigns integers to represent day of weeks: 1 is Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday, and 7 is Saturday.


Use User Defined Function to count the number of specific weekdays/weekends in a given month

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 June 2020, so I can apply this formula as one of below formulas, then press Enter key, and you will get how many Sundays at once. see screenshots:

=TotalDays(C2,C3,C4)

=TotalDays(2020,6,1)

 

Notes: This formulas use integers to represent day of weeks: 1 is Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday and 7 is Saturday.


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 Number of non-working days between two dates formula, Number of working days between two dates formula, and Count the number of specific weekday formula to quickly count the numbers of all weekends, weekends, or specific day of week in a date range in Excel.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

1. Select a blank cell you will place the counting result, and click Kutools > Formula Helper > Formula Helper to enable this feature

And then go ahead based on your counting types.

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

In the Formulas Helper dialog, please do as follows:
(1) Select Statistical from the Formula Type drop-down list;
(2) Click to select Number of non-working days between two dates in the Choose a formula list box;
(3) Enter the start date in the Start Date box (you can also refer a date cell);
(4) Enter the end date in the End Date box (you can also refer a date cell);
(5) Click the OK button.

Now it returns the total number of all Saturdays and Sundays in the selected cell.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

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

In the Formulas Helper dialog, please do as follows:
(1) Select Statistical from the Formula Type drop-down list;
(2) Click to select Number of working days between two dates in the Choose a formula list box;
(3) Enter the start date in the Start Date box (you can also refer a date cell);
(4) Enter the end date in the End Date box (you can also refer a date cell);
(5) Click the OK button.

And then it returns the total number of weekdays (excluding Saturdays and Sundays) in the selected cell.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

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

In the Formulas Helper dialog, please do as follows:
(1) Select Statistical from the Formula Type drop-down list;
(2) Click to select Count the number of specific weekday in the Choose a formula list box;
(3) Enter the start date in the Start Date box (you can also refer a date cell);
(4) Enter the end date in the End Date box (you can also refer a date cell);
(5) Specify the specific weekday with a integer (1 indicates Sunday, 2-5 means Monday to Friday, and 7 is Saturday.);
(6) Click the OK button.

And then it returns the total number of the specified weekday in the given date range.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Related articles:


The Best Office Productivity Tools

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. 60-day money back guarantee.
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