# How do I display the current quarter based on the date entered in another cell.

#### pjfreeman

##### New Member
I need a formula that will display the current quarter based on the date entered in another cell.

For example: if I have 08/14/14 in cell F7, I want 07/01/14 to 09/30/14 to display in cell B20. In cell B21 I want the following quarter dates to show (10/01/14 to 12/30/14). In cell B22 04/01/15 to 06/30/15 should show and in cell B23 I want 07/01/15 to 09/30/15.

The quarter dates should change with each date that is entered in cell F7, no matter what date is entered.

How is this done? I am using Excel 2010

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi and welcome to the MrExcel Message Board.

Try this in cell B20:

=TEXT(DATE(YEAR(\$F\$7),INT(MONTH(\$F\$7)/3)*3+(ROW(A1)-1)*3+1,1),"mm/dd/yy") & " to " & TEXT(DATE(YEAR(\$F\$7),(INT(MONTH(\$F\$7)/3)+1)*3+(ROW(A1)-1)*3+1,1)-1,"mm/dd/yy")

By the way, B22 becomes 01/01/15 to 03/31/15. I think you skipped a quarter?

<tbody>
</tbody>

This works too:

Cell Formulas
RangeFormula
B19=CHOOSE(ROUNDUP(MONTH(A19)/3,0),DATE(YEAR(A19),1,1),DATE(YEAR(A19),4,1),DATE(YEAR(A19),7,1),DATE(YEAR(A19),10,1))
B20=C19+1
B21=C20+1
B22=C21+1
B24=TEXT(B19,"mm-dd-yyy")&" to "&TEXT(C19,"mm-dd-yyy")
B25=TEXT(B20,"mm-dd-yyy")&" to "&TEXT(C20,"mm-dd-yyy")
B26=TEXT(B21,"mm-dd-yyy")&" to "&TEXT(C21,"mm-dd-yyy")
B27=TEXT(B22,"mm-dd-yyy")&" to "&TEXT(C22,"mm-dd-yyy")
C19=CHOOSE(ROUNDUP(MONTH(A19)/3,0),DATE(YEAR(A19),3,31),DATE(YEAR(A19),6,30),DATE(YEAR(A19),9,30),DATE(YEAR(A19),12,31))
C20=DATE(YEAR(B20),MONTH(B20)+2,DAY(EOMONTH(B20,2)))
C21=DATE(YEAR(B21),MONTH(B21)+2,DAY(EOMONTH(B21,2)))
C22=DATE(YEAR(B22),MONTH(B22)+2,DAY(EOMONTH(B22,2)))

That works perfectly!! Yes, I did skip a quarter (not intentionally). So, what would formula I put in B21, B22, etc. to show the following quarters?

Thanks!

To calculate the Quarter Date A1 is in, the single formula can be used:
=TEXT(DATE(YEAR(A1),((INT((MONTH(A1)-1)/3)+1)*3)-2,1),"MMMM dd, YYYY") & " to " & TEXT(EOMONTH(DATE(YEAR(A1),(INT((MONTH(A1)-1)/3)+1)*3,1),0),"MMMM dd, YYYY")

The beginning date for the quarter is calculated:
=DATE(YEAR(A1),((INT((MONTH(A1)-1)/3)+1)*3)-2,1)
and the ending date of the quarter is calculated:
=EOMONTH(DATE(YEAR(A1),(INT((MONTH(A1)-1)/3)+1)*3,1),0)

That works perfectly!! Yes, I did skip a quarter (not intentionally). So, what would formula I put in B21, B22, etc. to show the following quarters?

Thanks!

Just drag the formula down, it will make the appropriate changes as you do so.

Just drag the formula down, it will make the appropriate changes as you do so.
Rick,
I thought I was just supplementing with my post. I went back and in testing mine and finally went back and tested yours. It appears Rick's fails with date Dec 1, 2015... ?

Please check and test this formula:
(Deleted, was wrong for ending period year) back at it!

=TEXT(DATE(YEAR(\$A\$1),(((INT((MONTH(\$A\$1)-1)/3)+1+(ROW(A1)-1))*3)-2),1),"MMMM dd, YYYY") & " to " & TEXT(EOMONTH(DATE(YEAR(\$A\$1),(INT((MONTH(\$A\$1)-1)/3)+1+(ROW(A1)-1))*3,1),0),"MMMM dd, YYYY")

Last edited:
Well spotted SpillerBD.

I think this one is better:

=TEXT(DATE(YEAR(\$F\$7),INT((MONTH(\$F\$7)-1)/3)*3+(ROW(A1)-1)*3+1,1),"mm/dd/yy") & " to " & TEXT(DATE(YEAR(\$F\$7),(INT((MONTH(\$F\$7)-1)/3)+1)*3+(ROW(A1)-1)*3+1,1)-1,"mm/dd/yy")

Apologies for that.

Thank you ALL so much! I ended up using the one from DRSteele and it works great!
I truly appreciate your help and will recommend this site to anyone else needing excellent support for Excel.

I think this one is better:

=TEXT(DATE(YEAR(\$F\$7),INT((MONTH(\$F\$7)-1)/3)*3+(ROW(A1)-1)*3+1,1),"mm/dd/yy") & " to " & TEXT(DATE(YEAR(\$F\$7),(INT((MONTH(\$F\$7)-1)/3)+1)*3+(ROW(A1)-1)*3+1,1)-1,"mm/dd/yy")

Apologies for that.
I was surprised that yours wasn't correct to begin with! I triple checked I hadn't fouled things up copying from the forum.
Your revised formula doesn't use distributive property for the *3
In the month part where; INT((MONTH(\$F\$7)-1)/3)*3+(ROW(A1)-1)*3+1
(if I have it right) to INT(((MONTH(\$F\$7)-1)/3)+(ROW(A1)-1))*3+1
Somewhere in the back of my mind I do know the (1st day or month)-1 is better than using EOMONTH

(Comment to the peanut gallery: Doesn't matter here, but reducing the number of operations when a formula is applied to 1000's or 100k's cells can make a difference in performance.)

Replies
8
Views
86
Replies
9
Views
270
Replies
5
Views
360
Replies
1
Views
57
Replies
6
Views
210

1,203,632
Messages
6,056,439
Members
444,864
Latest member
Thundama

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back