# Thread: Formula for first four of the five completed calendar quarters

1. ## Formula for first four of the five completed calendar quarters

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!!!

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

3. 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! #NUM! #NUM! #NUM!

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•