Part II: Working with Formulas and Functions
Using the EOMONTH function
The EOMONTH function is an easy alternative to using the DATE function. With the
EOMONTH function, you can get the last date of any future or past month. All you need is
two arguments: a start date and the number of months in the future or past.
For example, this formula will return the last day of April 2015:
=EOMONTH("1/1/2015", 3)
Specifying a negative number of months will return a date in the past. This formula will
return the last day of October 2014:
=EOMONTH("1/1/2015", -3)
You can combine the EOMONTH function with the TODAY function to get the last day of the
current month.
=EOMONTH(TODAY(),0)
Calculating the calendar quarter for a date
Believe it or not, there is no built-in function to calculate quarter numbers in Excel. If you
need to calculate into which calendar quarter a specific date falls, you’ll need to create
your own formula.
Figure 12.11 demonstrates the following formula used for calculating calendar quarters:
=ROUNDUP(MONTH(B3)/3,0)
FIGURE 12.11
Calculating calendar quarters
The secret to this formula is simple math. Here you’re dividing the month number for the
given month by 3 and then rounding that number up to the nearest integer. For instance,
let’s say you want to calculate the quarter into which August falls. Since August is the 8th
month of the year, you could divide 8 by 3. That would give you the answer 2.66. Round
that number up, and you get 3. Thus, August is in the third quarter of the calendar year.
