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)}

Refer to Chapters 14 and 15 for more information about array formulas.

Figure 6-4 shows this formula used in a worksheet. In this case, the formula uses mixed cell

references so that you can copy it. For example, the formula in cell C3 is

{=SUM((WEEKDAY(DATE($B$2,$A3,ROW(INDIRECT(“1:”&

DAY(DATE($B$2,$A3+1,0))))))=C$1)*1)}

Figure 6-4:
Calculating the number of each weekday in each month of a year.