## 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** - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required!** Download 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** - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with 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** - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with 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** - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required!** Get It Now**

#### Related articles:

### Best Office Productivity Tools

**Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. ** **Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...**

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