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)

Another Sample here:

=INT((WEEKDAY($C$4-1)-$C$4+$C$5)/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)

Dim xindex As Integer

Dim endDate As Integerend

Date = 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**.

Ref: https://www.extendoffice.com/documents/excel/1421-excel-count-mondays-sundays-between-two-dates.html