Microsoft Office Tutorials and References
In Depth Information
Date-Related Functions
Counting the occurrences of a day of the week
You can use the following formula to count the number of occurrences of a particular day of the
week for a specified month. It assumes that cell A1 contains a date and that cell B1 contains a day
number (1 for Sunday, 2 for Monday, and so on). The formula is an array formula, so you must
enter it by pressing Ctrl+Shift+Enter.
{=SUM((WEEKDAY(DATE(YEAR(A1),MONTH(A1),ROW(INDIRECT(“1:”&
DAY(DATE(YEAR(A1),MONTH(A1)+1,0))))))=B1)*1)}
If cell A1 contains the date January 8, 2010, and cell B1 contains the value 3 (for Tuesday), the
formula returns 4, which reveals that January 2010 contains four Tuesdays.
The preceding array formula calculates the year and month by using the YEAR and MONTH
functions. You can simplify the formula a bit if you store the year and month in separate cells. The
following formula (also an array formula) assumes that the year appears in cell A1, the month in cell
A2, and the day number in cell B1:
{=SUM((WEEKDAY(DATE(A1,A2,ROW(INDIRECT(“1:”&
DAY(DATE(A1,A2+1,0))))))=B1)*1)}