How to find previous quarter and display as Q3 or Q4 etc

  • Hi, I would like to be able to determine the previous quarter from the current date and display as Q2 or Q3 or Q4 etc.


    I have the code to find and display the previous month (see below) but am unable to find a similar one for quarter. Thanks in advance.

    Code
    1. Range("A4") = Format(DateAdd("m", -1, Date), " mmm yy")
  • Hi,


    You can test following formula :


    Code
    1. ="Q"&ROUNDUP((MONTH(TODAY())/3)-1,0)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    Edited once, last by Carim ().

  • Just noticed you might need a macro solution ...


    Code
    1. Sub TestPreviousQuarter()
    2. MsgBox Evaluate("=""Q""&ROUNDUP((MONTH(Today())/3)-1,0)")
    3. End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Just noticed you might need a macro solution ...


    Code
    1. Sub TestPreviousQuarter()
    2. MsgBox Evaluate("=""Q""&ROUNDUP((MONTH(Today())/3)-1,0)")
    3. End Sub

    Hi Carim I used your code for this quarter and it's fine but I changed the time and date on my computer to Feb 2020 test it for next quarter and it returned Q-1 instead of Q4. All other quarters were fine when I also tested them. What do you think the problem might be. The exact code I used is below. Thanks in advance. PS, Ingo's doesn't work either.

    Code
    1. Range("F1") = Evaluate("=""Q""&ROUNDUP((MONTH(Today())/3)-1,0)")
  • Hello,


    If the reference date is the one of your computer ... just replace Today() by Date


    Code
    1. Sub TestPreviousQuarter()
    2. MsgBox Evaluate("=""Q""&ROUNDUP((MONTH(Date)/3)-1,0)")
    3. End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi, I use the macro at work where I can't change the time on my computer....I was test it at home on my computer.


    I assume the reference date would be on the computer....I don't reference a date when I run the macro.


    What do you think I should do, just leave today or replace it with date?

  • Hi again,


    Basically the question is the following :


    If your computer's date is correct i.e. if it displays the correct date ...


    Today() and Date are totally identical ... ;)


    It is only if you are confronted with very specific constraints that these two instructions can differ ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi,


    To be on the safe side, below is the adjusted macro


    Code
    1. Sub TestPreviousQuarter()
    2. MsgBox Evaluate("=""Q""&LOOKUP(ROUNDUP(MONTH(Today())/3,0),{1,2,3,4},{4,1,2,3})")
    3. End Sub

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi, I assume I need to insert the date in A1 before I run the macro. Unfortunately that is not an option because the report is automatically sent to several people and they will be running the macro from their computers. I need to be able to just open the report and run the macro.


    So if your original one works I will stick with it.....will it work?

  • Hi,


    Let me clarify ...


    The test file is only designed to test ... and therefore validate the new formula ...


    The original macro will not work ...


    The macro you need to use is the following one :


    Code
    1. Sub TestPreviousQuarter()
    2. MsgBox Evaluate("=""Q""&LOOKUP(ROUNDUP(MONTH(Today())/3,0),{1,2,3,4},{4,1,2,3})")
    3. End Sub


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)