Formula for first four of the five completed calendar quarters

oxicottin

New member
Joined
Nov 17, 2012
Messages
2
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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

New member
Joined
Nov 17, 2012
Messages
2
Reaction score
0
Points
0
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!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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")

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

What a surprise.
 
Top