|   When you're creating calendar- related applications, especially payroll applications, you sometimes need to know how many times a given day of the week appears in a particular month . 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: =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 settings12/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 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. | 