Formula for first four of the five completed calendar quarters

**oxicottin** · 2012-11-17, 03:06 AM

Hello, I'm trying to make a base period form from a (Start Date) I enter that would show the first four of five completed calendar quarters prior to the date (Start Date)

EXAMPLE:

(Start Date) = 11/16/2012

June 30-2012 To April 1-2012

March 31-2012 To January 1-2012

December 31-2011 To October 31-2011

September 30-2011 To June 30-2011

I wanted to enter the start date in cell (A1) then have the quarters follow in the text/date format.

(A1) = Start date

Cell (A3) = June 30-2012 To April 1-2012

Cell (A4) = March 31-2012 To January 1-2012

Cell (A5) = December 31-2011 To October 31-2011

Cell (A6) = September 30-2011 To June 30-2011

Thanks!!!

**Bob Phillips** · 2012-11-17, 08:52 AM

Try

=TEXT(DATE(YEAR($A$1),MONTH($A$1)+1-((ROW(A1)-1)*3),0),"mmmm dd-yyyy")&" To "&TEXT(DATE(YEAR($A$1),MONTH($A$1)+1-(ROW(A1)*3),1),"mmmm dd-yyyy")

**oxicottin** · 2012-11-17, 01:31 PM

It gives me the wrong dates;

11/17/2012

November 30-2012 To September 01-2012

August 31-2012 To June 01-2012

May 31-2012 To March 01-2012

February 29-2012 To December 01-2011

I need it to be like;

11/17/2012

June 30-2012 to April 1-2012

March 31-2012 to January 1-2012

December 31-2011 to October 1-2011

September 30-2011 to July 1-2011

Also it gives an error if I take the start date out;

#NUM!

**Bob Phillips** · 2012-11-22, 08:38 PM

Originally Posted by oxicottin

It gives me the wrong dates;

OK, try this alternative

=TEXT(DATE(YEAR($A$1),INT((MONTH($A$1)-3)/3)*3+1-((ROW(A1)-1)*3),0),"mmmm dd-yyyy")&" To "&TEXT(DATE(YEAR($A$1),INT((MONTH($A$1)-3)/3)*3+1-(ROW(A1)*3),1),"mmmm dd-yyyy")

Originally Posted by oxicottin

Also it gives an error if I take the start date out;

What a surprise.