Microsoft Office Tutorials and References
In Depth Information
Determining the last day of 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 June 2010, it returns June 11,
2010.
Determining the last day of a month
To determine the date that corresponds to the last day of a month, you can use the DATE
function. However, you need to increment the month by 1 and use a day value of 0. In other words,
the “0th” day of the next month is the last day of the current month.
The following formula assumes that a date is stored in cell A1. The formula returns the date that
corresponds to the last day of the month:
=DATE(YEAR(A1),MONTH(A1)+1,0)
You can use a variation of this formula to determine how many days comprise a specified month.
The following formula returns an integer that corresponds to the number of days in the month for
the date in cell A1 (make sure that you format the cell as a number, not as a date):
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
Determining a date’s quarter
For financial reports, you might find it useful to present information in terms of quarters. The
following formula returns an integer between 1 and 4 that corresponds to the calendar quarter for
the date in cell A1:
=ROUNDUP(MONTH(A1)/3,0)
This formula divides the month number by 3 and then rounds up the result.

Search JabSto ::

Custom Search