Categories
Excel

Count All Occurrence of a Specific Day in a Month or Two Dates

In Excel, you can determine the number of specified days (e.g., Mondays, Tuesdays, etc.) in any given month or (between two dates) using some methods described in this tutorial.

  1. Use NetworkDays.Intl Function to Find the Occurrence of a Specific Weekday.
  2. Using a Custom Function.

Use NETWORKDAYS.INTL to Determine the Number of Specified Days in Any Month

The NETWORKDAYS.INTL function is available in Microsoft Excel 2010 and later versions, including Excel for Microsoft 365.

This function allows you to customize the weekends and specify which days of the week are considered as weekends. It returns the number of working days between two dates.

The syntax of the NETWORKDAYS.INTL function is as follows:

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

The weekend argument is a string that specifies which days of the week are considered weekends. It entered as a text string of seven 1s and 0s, for example, 1000111. A 1 is entered to indicate a non-working day, and a 0 is entered for a working day. The first character represents a Monday and the other six for the remaining days of the week.

1. Count the Number of Mondays Between Two Dates (or in a Month)

The following formula specifies that only Monday is the working day in August:

=NETWORKDAYS.INTL("01 Aug 2023","31 Aug 2023","0111111")

The result of this formula is 4, which indicates that there are 4 Mondays in the month of August, 2023.

2. Count the Number of Tuesdays Between Two Dates (or in a Month)

The following formula specifies that only Tuesday is the working day in August:

=NETWORKDAYS.INTL("01 Aug 2023","31 Aug 2023","1011111")

The result of this formula is 5, which indicates that there are 5 Tuesdays in the month of August, 2023.

3. Count the Number of Wednesdays Between Two Dates (or in a Month)

The following formula specifies that only Wednesday is the working day in August:

=NETWORKDAYS.INTL("01 Aug 2023","31 Aug 2023","1101111")

The result of this formula is 5, which indicates that there are 5 Wednesdays in the month of August, 2023.

4. Count the Number of Thursdays Between Two Dates (or in a Month)

The following formula specifies that only Thursdays is the working day in August:

=NETWORKDAYS.INTL("01 Aug 2023","31 Aug 2023","1110111")

The result of this formula is 5, which indicates that there are 5 Thursdays in the month of August, 2023.

5. Count the Number of Fridays Between Two Dates (or in a Month)

The following formula specifies that only Friday is the working day in August:

=NETWORKDAYS.INTL("01 Aug 2023","31 Aug 2023","1111011")

The result of this formula is 4, which indicates that there are 4 Fridays in the month of August, 2023.

6. Count the Number of Saturdays Between Two Dates (or in a Month)

The following formula specifies that only Saturday is the working day in August:

=NETWORKDAYS.INTL("01 Aug 2023","31 Aug 2023","1111101")

The result of this formula is 4, which indicates that there are 4 Saturdays in the month of August, 2023.

7. Count the Number of Sundays Between Two Dates (or in a Month)

The following formula specifies that only Sunday is the working day in August:

=NETWORKDAYS.INTL("01 Aug 2023","31 Aug 2023","1111110")

The result of this formula is 4, which indicates that there are 4 Sundays in the month of August, 2023.

Use VBA to Find the Number of Specified Days in Any Month

If you’re using Excel 2007 or earlier, the NETWORKDAYS.INTL function might not be available to you. The older Excel versions have many date and time functions, they do not have a date and time function that will, for example, tell you how many Mondays are in the month of January in the year 2000.

This is a case in which VBA can simplify a complicated task. Instead of fumbling with complex functions, you can write a custom function that will do the same thing, and all you need to do is input the day and date for which you want a count.

You can use the following function to determine how many days are in any specified month. For example:

=HowManyDaysInMonth("1/12/03","wed")

will return 5, as there were five Wednesdays in the month of December in 2003. (Note that the date format should match your local settings-12/1/03 in the United States, for instance. The date format in the example is from Australia.)

Similarly, the following function:

=HowManyDaysInMonth("1/12/03","thu")

will return 4, as there were four Thursdays in the month of December in 2003.

To use this custom function in a workbook, you must first place the following code into a standard module, so open the workbook into which you want to place the code and open Visual Basic Editor (Alt+F11 or Option+F11). Then select Insert » Module and paste in the following code:

'The Code
Function HowManyDaysInMonth(FullDate As String, sDay As String) As Integer
Dim i As Integer
Dim iDay As Integer, iMatchDay As Integer
Dim iDaysInMonth As Integer
Dim FullDateNew As Date
iMatchDay = Weekday(FullDate)
    Select Case UCase(sDay)
       Case "SUN"
        iDay = 1
       Case "MON"
        iDay = 2
       Case "TUE"
        iDay = 3
       Case "WED"
        iDay = 4
       Case "THU"
        iDay = 5
       Case "FRI"
        iDay = 6
       Case "SAT"
        iDay = 7
    End Select
  iDaysInMonth = Day(DateAdd("d", -1, DateSerial _
            (Year(FullDate), Month(FullDate) + 1, 1)))
  FullDateNew = DateSerial(Year(FullDate), Month(FullDate), iDaysInMonth)
        For i = iDaysInMonth - 1 To 0 Step -1
            If Weekday(FullDateNew - i) = iDay Then
                HowManyDaysInMonth = HowManyDaysInMonth + 1
            End If
        Next i
End Function

Close the window to return to Excel.

Now enter the function into any cell as shown earlier, and Excel will return a number that represents how many times the specified day occurred in the specified month.

=HowManyDaysInMonth("1/Aug/2023", "Mon")  returns 4
=HowManyDaysInMonth("1/Aug/2023", "Tue")  returns 5
=HowManyDaysInMonth("1/Aug/2023", "Wed")  returns 5
=HowManyDaysInMonth("1/Aug/2023", "Thu")  returns 5
=HowManyDaysInMonth("1/Aug/2023", "Fri")  returns 4
=HowManyDaysInMonth("1/Aug/2023", "Sat")  returns 4
=HowManyDaysInMonth("1/Aug/2023", "Sun")  returns 4

Date and Time

  1. How Date and Time Feature Works in Excel
  2. Calculate Date and Time
  3. Display Negative Time Values
  4. Count All Occurrence of a Specific Day in a Month or Two Dates
  5. Calculate Working Days and Holidays
  6. Determine Deadlines, Schedules, or Due Dates
  7. Show Total Time as Days, Hours, and Minutes
  8. Format Date and Time Values