Counting weekdays

Is it possible to figure out how many occurrences of a certain weekday there are in a specific month? I need to figure out how many Thursdays in each month of the year.

By Neale Blackwood

Yes, but the formula has 6 IF functions in it. Assume cell A2 has the date of the first day of the month involved, for example 1/2/08 for February 2008. Cell B2 has the text of the three-letter abbreviation for the day being searched, for example, Thu for Thursday. The formula in cell C2 is:

=IF(MONTH(A2)=MONTH(A2+30),

IF(OR(TEXT(A2,”ddd”)=B2,TEXT(A2+1,”ddd”)=B2,TEXT(A2+2,”ddd”)=B2),5,4),

IF(MONTH(A2)=MONTH(A2+29),

IF(OR(TEXT(A2,”ddd”)=B2,TEXT(A2+1,”ddd”)=B2),5,4),

IF(MONTH(A2)=MONTH(A2+28),

IF(TEXT(A2,”ddd”)=B2,5,4),4)))

I’ve split the formula into six lines to make it easier to follow.

The idea behind the formula is that there are only three possible month lengths — 29, 30 and 31 days — that can have more than four occurrences of a day. Also, only the days that occur at the start of the month can occur five times.

The first IF is checking to see if the month has 31 days. If it has then the second IF determines if the day in cell B2 matches one of the first three days using an OR function. The third IF determines if the month has 30 days, if it has the fourth IF function figures out if the day in B2 is in the first two days of the month. The fifth IF determines if the month is 29 days long, if it is then the sixth IF function figures out if the day in B2 is the first day of the month. In the second, fourth and sixth
IF functions, if the day is found to occur at the start of the month then ‘5’ is displayed otherwise ‘4’ is returned.

The last 4 in the formula handles a normal February. The TEXT functions are used to convert the date in cell A2 to a three-character day text to compare with the entry in cell B2. You can copy the above formula from this article on the CPA website to save typing it.

---

Extras

Rob Steinhoff, Associate, Clarkson ITT sent through a better solution using the SUMPRODUCT formula.

=SUMPRODUCT((TEXT(D3+ROW(A1:A31)-1,”dddd”)=D5)*(MONTH(D3+ROW(A1:A31)-1)=MONTH(D3)))