Microsoft Office Tutorials and References
In Depth Information
Date-Related Functions
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 zero (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 make up a specified month.
The formula that follows returns an integer that corresponds to the number of days in the month
for the date in cell A1.
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
Determining whether a year is a leap year
To determine whether a particular year is a leap year, you can write a formula that determines
whether the 29th day of February occurs in February or March. You can take advantage of the
fact that Excel’s DATE function adjusts the result when you supply an invalid argument — for
example, a day of 29 when February contains only 28 days.
The following formula returns TRUE if the year of the date in cell A1 is a leap year; otherwise, it
returns FALSE:
=IF(MONTH(DATE(YEAR(A1),2,29))=2,TRUE,FALSE)
This function returns the wrong result (TRUE) if the year is 1900. See the section “The
Excel leap year bug,” earlier in this chapter.
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