Microsoft Office Tutorials and References
In Depth Information
Date-Related Functions
To modify this formula to find the date of a day other than Sunday, change the 1 to a different
number between 2 (for Monday) and 7 (for Saturday).
Determining the first day of the week after a date
This next formula returns the specified day of the week that occurs after a particular date. For
example, use this formula to determine the date of the first Monday after June 1, 2010. The
formula assumes that cell A1 contains a date and that cell A2 contains a number between 1 and 7 (1
for Sunday, 2 for Monday, and so on).
=A1+A2-WEEKDAY(A1)+(A2<WEEKDAY(A1))*7
If cell A1 contains June 1, 2010 (a Tuesday), and cell A2 contains 7 (for Saturday), the formula
returns June 5, 2010. This is the first Saturday after June 1, 2010.
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
h Cell A1 contains a year.
h Cell A2 contains a month.
h Cell A3 contains a day number (1 for Sunday, 2 for Monday, and so on).
h Cell A4 contains the occurrence number (for example, 2 to select the second occurrence
of the weekday specified in cell A3).
If you use this formula to determine the date of the second Friday in November 2010, it returns
November 12, 2010.
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 November, 2010 (there is no such date), the formula
returns the first Friday in December.

Search JabSto ::

Custom Search