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")
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!!!
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")
It gives me the wrong dates;
I need it to be like;
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
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!
Bookmarks