How to: Get the Quarter End Date for any input date

There are a number of methods open to us in Excel in to allow us to calculate the Quarter End date of a given input date. Find the one which fits your circumstances best and how they work.

Which method you choose will generally be what you feel most comfortable using, or what you feel is more intuitive to you.

NOTE: For production system, or where you share your model or spreadsheet with others, and you have a choice of options, other factors such as speed of performance or complexity or maintenance become a consideration.

For small processes any of the following will produce the answer you require.

Similar in approach to calculating the First Day of the Month or Last Day of the Month, we have the choice of a number of approaches. Typically using EOMONTH or DATE functions.

In the following examples we will assume A1 contains our input date. The following code will return the quarter end date.

Method 1

Using the EOMONTH, MONTH and MOD functions:

The principle here is we use the EOMONTH function and calculated the number of months to add on to get to the next quarterly month. EOMONTH returns the date on the last day of that month.

=EOMONTH(A1,MOD(3-MONTH(A1),3))

How it works

The EOMONTH function takes two arguments; the first argument accepts a date, and returns the end of the month date, but shifted forward or backward by the a number of months stated by the second argument.

For example below, the EOMONTH function will return the end of the month of the input date in cell A1, shifted by zero months, Therefore returning the date of the end of the month of the input date

=EOMONTH(A1,0)

So if A1 contains the date January 1, 2020, the output of the formula above will be January 31, 2020.

Calculating the number of months to shift our month end date

We next want to calculate how many months to shift our EOMONTH month end date by. Knowing that 3 is the number of months in a quarter, we can calculate how many months into each quarter our month number is, using the MOD functions.

The MONTH function

The Month function returns the month number of any date from 1 to 12.

The MOD function

We then use the MOD function to calculate how many months to shift the month-end date to get to the next quarter end (which is a multiple of 3).

The Syntax of MOD

=MOD(number, divisor)

The MOD function works by removing whole number multiples of the divisor from the number, until you have a value less than the devisor left. This value is the output of the MOD function.

For example,

MOD(6,3) = 0

3 divides into 6 perfectly with no remaining whole number so we get 0.

However:

MOD(8,3) = 2

3 divides into 8 twice, with 2 left over. The MOD function returns 2.

MOD with numbers smaller than the divisor

Where the number is smaller than the divisor, the result is just the number:

MOD(2,3) = 2

MOD function with negative numbers

However the number is negative, MOD works slightly differently.

Firstly, the output of MOD (for both positive and negative numbers) preserves the sign in the output of the divisor.

Using the example above, but by changing the number 8 to -8, we get a different answer:

MOD(-8,3) = 1

As before the MOD function returns the value remaining after multiples of the divisor have been removed.

NOTE: The multiple of the divisor removed from the number to leave a remainder, must be the multiple which is equal to or lower (or more negative) than the input number.

So for a divisor of 3, its first multiple lower than our number -8, is -9. (similar to our original example where the first multiple lower than +8 was +6).

The difference left over is between -9 and -8 is 1, that is “(-8) – (-9) = 1”. Therefore MOD(-8, 3) returns 1

Expected Results from our MOD function

Month	Month number	MOD result	Quarter End Month
January	1	2
February	2	1
March	3	0	✔
April	4	2
May	5	1
June	6	0	✔
July	7	2
August	8	1
September	9	0	✔
October	10	2
November	11	1
December	12	0	✔

Putting it all together:

Our divisor is 3, the number of months in a quarter
for months 1, 4, 7 & 10 we want to return 2 from the MOD function
for months 2, 5, 8 & 11 we want to return 1 from the MOD function
for months 3, 4, 9 & 12 we want to return 0 from the MOD function, this is already the month of the quarter end
By first subtracting our months from 3, we get the difference from three, rather than the difference from 0, since we will be shifting our EOMONTH function to the nearest next quarter end.
The EOMONTH function provides the month end date for the input date, and the MOD function calculates the shift or 0, 1, or 2 months required to find the end of the following quarter.

Method 2

Using the DATE, MONTH, YEAR and ROUNDUP functions:

=DATE(YEAR(A1),(ROUNDUP(MONTH(A1)/3,0)*3)+1,0)

How it works

In this case we are using DATE to form quarter end date by manipulating the day and month arguments.

The DATE function takes 3 arguments to form a date: a year number, and month number and a day number.

=DATE(year_num, month_num, day_num)

By setting the day to zero, this is equivalent to a date one day prior to the first day of the month, i.e. the last day of the preceding month.

So, we can always find the end of any given month by setting the day argument in DATE to zero.

Therefore all we need to do next is categorise the dates with months January, February & March (i.e. months 1, 2 & 3) into Q1 April, May and June (i.e. months 4, 5 & 6) in to Q2, and so on.

However because we set the day argument to refer to the end of the month prior, we need to add one month to refer to the current month instead.

To bucket the months, we take the month number of the input date, divide that by 3 and round the result up to the nearest integer.

The ROUNDUP function takes 2 arguments, the number to be rounded, and num_digits, or precision, to which digit the number should round up to.

When the num_digits argument is zero, this means roundup to the nearest whole number (or integer).

Therefore, the ROUNDUP part of the formula will take any number 1-12 and bucket them into 4 distinct groups.

ROUNDUP(MONTH(A1)/3,0)

Months 1, 2 & 3 return 1.
Months 4, 5 & 6 return 2.
Months 7, 8 & 9 return 3, and
Months 10, 11 & 12 return 4.

But since we want the “months of the quarter ends” and not just the quarter numbers, the quarter buckets are multiplied by 3 to give 3, 6, 9 & 12 respectively.

Finally to cater for the day part of DATE referring to the previous month (as day = 0) effectively deducting 1 month from the date, we will add one to each date’s quarter month , effective creating quarter end months of 4, 7, 10 & and 13 and cancelling out the effect of the day argument on the date function.

Therefore we return a date made up of the

year,
nearest next quarter end month + 1, and
the last day of the month prior,

which gives us our quarter end date.

Method 3

Using the DATE, YEAR, MONTH and INT functions:

We could have calculated our month buckets in various ways, Method 2 used ROUNDUP, in Method 3 INT to return a month integer.

The month will be returned by the following formula:

((INT((MONTH(A1)-1)/3)+1)*3) +1

Again using the DATE function, this time instead of ROUNDUP we use INT to drop the decimals caused by dividing the month numbers

=DATE(YEAR(A1),((INT((MONTH(A1)-1)/3)+1)*3) +1, 1) -1

How it works

A variation on Method 2, we set the day argument initially to 1, calculate quarter end buckets from the month numbers, and add one month

Finally and subtract 1 day