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
    Range("A4") = Format(DateAdd("m", -1, Date), " mmm yy")

    Hi,


    You can test following formula :


    Code
    ="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 "Smiley" icon, below, in the bottom right corner :)

    Edited once, last by Carim ().

    Just noticed you might need a macro solution ...


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

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

    Love 1
    Quote from cobber1418

    Perfect....thanks heaps.

    Thanks for your Thanks AND for the Like :)

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

    Love 1
    Quote from Carim

    Just noticed you might need a macro solution ...


    Code
    Sub TestPreviousQuarter()
  MsgBox Evaluate("=""Q""&ROUNDUP((MONTH(Today())/3)-1,0)")
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
    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
    Sub TestPreviousQuarter()
 MsgBox Evaluate("=""Q""&ROUNDUP((MONTH(Date)/3)-1,0)")
End Sub


    Hope this will help

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

    Love 1

    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 "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Hi, the date on the computer will be the days date when I run it......so the way I was testing it was the problem?


    I assume if I run your code actually in Feb it will be OK....is that correct?

    Hi,


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


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

    Hope this will help

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

    Love 1

    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
    Sub TestPreviousQuarter()
 MsgBox Evaluate("=""Q""&LOOKUP(ROUNDUP(MONTH(Today())/3,0),{1,2,3,4},{4,1,2,3})")
End Sub


    Hope this clarifies

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

    Love 1

    An alternative

    Code
    [f1] = "Q" & DatePart("q", DateAdd("m", -3, Date))

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Love 1

    Glad you could fix your problem ;)


    Thanks for your Thanks AND for the Like :)

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

    Love 1

    You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Love 1

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login