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

[pjfreeman]

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

 

[RickXL]

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?[TABLE="width: 245"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

This works too:

Excel 2012
	A	B	C
19	November-14-15	October-01-15	December-31-15
20		January-01-16	March-31-16
21		April-01-16	June-30-16
22		July-01-16	September-30-16
23
24		10-01-2015 to 12-31-2015
25		01-01-2016 to 03-31-2016
26		04-01-2016 to 06-30-2016
27		07-01-2016 to 09-30-2016
Sheet16

Cell Formulas
Range	Formula
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)))

 

[pjfreeman]

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!

 

[SpillerBD]

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)

 

[RickXL]

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.

 

[SpillerBD]

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")

 

[RickXL]

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.

 

[pjfreeman]

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.

 

[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.

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.)