Neale Blackwood counts the days in a month
Q: 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.
A: 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.
Neale Blackwood CPA is a senior business analyst with Access Analytic Solutions, which provides financial modelling and management reporting services, as well as advanced Excel training. Email: nblackwood@accessanalytic.com.au
For back issues visit the Tips for using Mircosoft Excel section.
Reference: August 2008, volume 78:07, p. 20
