Results 1 to 4 of 4

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

  1. #1

    Formula for first four of the five completed calendar quarters



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #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. #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. #4
    Quote Originally Posted by oxicottin View Post
    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")

    Quote Originally Posted by oxicottin View Post
    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
  •