Microsoft Office Tutorials and References
In Depth Information
Determining the first day of the week after a date
Determining the i rst day of the week after a date
This formula returns the specifi ed day of the week that occurs after a particular date. For
example, use this formula to determine the date of the fi rst Monday after a particular date.
The formula assumes that cell A1 contains a date and 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, 2013 (a Saturday), and cell A2 contains 2 (for Monday), the
formula returns June 3, 2013 . This is the fi rst 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 that 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 specifi ed in cell A3).
If you use this formula to determine the date of the second Friday in November 2013, it
returns November 8,2013 .
If the value in cell A4 exceeds the number of the specii ed day in the month, the formula returns a date from a
subsequent month. For example, if you attempt to determine the date of the i fth Friday in October 2013 (there is no such
date), the formula returns the i rst Friday in November.
Calculating dates of holidays
Determining the date for a particular holiday can be tricky. Some, such as New Year’s Day
and Independence Day in the United States always occur on the same date. For these kinds
of holidays, you can simply use the DATE function. To enter New Year’s Day (which always
falls on January 1) for a specifi c year in cell A1, you can enter this function:
=DATE(A1,1,1)
Search JabSto ::




Custom Search