## 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****Use formula to count the number of specific weekday in month****Use VAB to count the number of specific weekday in a month****Count numbers of all weekends/weekdays/specific day of week between two dates**

#### **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.

#### 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!