+ Reply to Thread
Results 1 to 6 of 6

How to return last quarter months?

  1. #1
    Forum Contributor
    Join Date
    09-19-2016
    Location
    Alaska
    MS-Off Ver
    MS 2010, 2013, 2016
    Posts
    272

    How to return last quarter months?

    Hi - I have the three formulas below that return April, May and June but will change to May, June and July once its August. Is there a way to keep those same three months until the 3rd quarter (September) is over?

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Basically, I need to pull the previous quarter months. So if were in quarter 1 (January-March), I would need quarter 4 (October-December) to be returned.
    Last edited by AccountingJ; 07-03-2020 at 02:34 PM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: How to return last quarter months?

    Please try at A3:A5

    =TEXT(EDATE(TODAY(),1-MONTH(TODAY())+FLOOR(MONTH(TODAY())-1,3)-ROWS(A3:A$5)),"mmm")

  3. #3
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: How to return last quarter months?

    Here is another formula that can be considered (place it in a cell and copy it down for two more cells)...

    =TEXT(87*ROUNDUP(MONTH(TODAY())/3,0)-29*(3-ROWS($1:1)),"mmmm")


    EDIT NOTE
    ----------------------------------------
    For those of you who may be wondering about the underlying structure for the above formula, I discovered many, many, many, many years ago that you could get the month name from the month number using this structure...

    =TEXT(29*MonthNumber,"mmmm")
    Last edited by Rick Rothstein; 07-03-2020 at 02:26 PM.

  4. #4
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    483

    Re: How to return last quarter months?

    Another set of formulas:

    =TEXT((MOD(CEILING(MONTH(TODAY()),3)+6,12)+1)*29,"mmmm")
    =TEXT((MOD(CEILING(MONTH(TODAY()),3)+6,12)+2)*29,"mmmm")
    =TEXT((MOD(CEILING(MONTH(TODAY()),3)+6,12)+3)*29,"mmmm")

  5. #5
    Forum Contributor
    Join Date
    09-19-2016
    Location
    Alaska
    MS-Off Ver
    MS 2010, 2013, 2016
    Posts
    272

    Re: How to return last quarter months?

    Thank you all for taking your time and responding, its nice to see multiple ways of getting the same result.

  6. #6
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: How to return last quarter months?

    If the OP wants to use 3 separate formulas instead of a single formula copied down, what I posted in Message #3 comes out as this...

    =TEXT(87*ROUNDUP(MONTH(TODAY())/3,0)-58,"mmmm")
    =TEXT(87*ROUNDUP(MONTH(TODAY())/3,0)-29,"mmmm")
    =TEXT(87*ROUNDUP(MONTH(TODAY())/3,0),"mmmm")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Months defined from Quarter--IF FORMULA?
    By julesmctavish in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-27-2016, 01:28 PM
  2. Adding months to dates and calculating beginning of next quarter
    By mtmirabal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2014, 01:01 PM
  3. Add a Quarter Column to my Months Columns
    By zicitron in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-01-2013, 05:41 AM
  4. Quarter accrual on a 12 months basis
    By darkphoenix in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-17-2012, 07:47 AM
  5. Display Quarter in Months
    By Piper1509 in forum Excel General
    Replies: 5
    Last Post: 08-05-2010, 06:34 AM
  6. Determine Quarter for Fiscal Calendar Months
    By John Bates in forum Excel General
    Replies: 3
    Last Post: 10-24-2009, 11:00 PM
  7. [SOLVED] Fill Sum of months by quarter
    By vmagal1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2006, 02:50 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1