Determine the Number of Specified Days in Any Month

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.