Microsoft Office Tutorials and References
In Depth Information
Determining the date of the most recent Sunday
Determining the date of the most recent Sunday
The formula in this section returns the most recent specified day. You can use the following
formula to return the date for the previous Sunday. If the current day is Sunday, the formula returns
the current date. The result is a date serial number (you need to format the cell to display as a
readable date):
=TODAY()-MOD(TODAY()-1,7)
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
The following formula returns the specified day of the week that occurs after a particular date.
For example, you can use this formula to determine the date of the first Friday after July 4, 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 July 4, 2010, and cell A2 contains 6 (for Friday), the formula returns July 9,
2010. It’s the first Friday after July 4, 2010 (which is a Sunday).
Determining the nth occurrence of a day of the week in a month
You might need a formula to determine the date for a particular occurrence of a weekday.
Suppose that your company payday falls on the second Friday of each month and that 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
This formula assumes that
h Cell A1 contains a year.
h Cell A2 contains a month.
 
Search JabSto ::




Custom Search