Microsoft Office Tutorials and References
In Depth Information
=A1+A2-WEEKDAY(A1)+(A2<WEEKDAY(A1))*7
If cell A1 contains June 1, 2013 (a Saturday) and cell A2 contains 2 (for Monday), the formula returns June 3,
2013. This is the first Monday following June 1, 2013.
Determining the nth occurrence of a day of the week in a
month
You may need a formula to determine the date for a particular occurrence of a weekday. For example, suppose
your company payday falls on the second Friday of each month, and you need to determine the paydays for
each month of the year. The following formula makes this type of calculation:
=DATE(A1,A2,1)+A3-WEEKDAY(DATE(A1,A2,1))+(A4-(A3>=WEEKDAY(DATE(A1,A2,1))))*7
The formula in this section assumes that
• Cell A1 contains a year.
• Cell A2 contains a month.
• Cell A3 contains a day number (1 for Sunday, 2 for Monday, and so on).
• Cell A4 contains the occurrence number (for example, 2 to select the second occurrence of the weekday spe-
cified in cell A3).
If you use this formula to determine the date of the second Tuesday in November 2013, it returns November 12,
2013.
If the value in cell A4 exceeds the number of the specified day in the month, the formula
returns a date from a subsequent month. For example, if you attempt to determine the
date of the fifth Friday in October, 2013 (there is no such date), the formula returns the
first Friday in November.
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 spe-
cified 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, 2013 and cell B1 contains the value 3 (for Tuesday), the formula returns
5, which reveals that January 2013 contains five 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:
Search JabSto ::

Custom Search