Although Excel has many date and time functions, at the time of this writing, it does 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 2005. You could use a very deeply nested variety of Excel's date and time functions to figure this out, but unfortunately, as you can imagine, this would be very cumbersome and awkward to reproduce.
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 may days are in any specified month. For example:
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:
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 select Tools » Macro » Visual Basic Editor (Alt/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 simply 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.updated