CODEWITHSUNDEEP
vbaexcel
Mayou
Mayou

Reputation: 8818

Finding Previous End of Quarter in VBA

I am trying to find the previous end of quarter given the current date, formatted such that I only return the month name.

For example, today is "9/9/2014", I would like to return "June", since this is the month that marks the previous end of quarter.

Format(DateAdd("m", -3, Now), "MMMM")

But this implies that I need to hardcode how many months I have to go back, which is not what I am trying to achieve. Please bear with me as I am a beginner at VBA.

Any help would be appreciated! Thanks!

Upvotes: 0

Views: 4797

Answers (5)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

And here is a "one-liner", since your quarter ends are Mar, Jun, Sep, Dec:

Format(DateAdd("m", -(Month(DT) - 1) Mod 3 - 1, DT), "mmmm")

if DT is some date. For Today, replace DT with Date

To return a real date, which you can format in a worksheet cell as a date, remove the Format function and just use the central part

Upvotes: 2

Dick Kusleika
Dick Kusleika

Reputation: 33145

You want a function that returns a date, not a string. Get the right date, then use the existing Format function to get the string. Then later on when you want the actual date, you don't have write a new function.

Public Function EndOfLastQuarter(ByVal dtNow As Date) As Date

    EndOfLastQuarter = DateSerial(Year(dtNow), (((Month(dtNow) - 1) \ 3) * 3) + 1, 0)

End Function

?Format(EndOfLastQuarter(Now),"mmmm")
June

Upvotes: 2

user3745460
user3745460

Reputation: 11

Looks like there's plenty of answers for this one already, but allow me to propose another:

Choose(worksheetfunction.Roundup(month(Date)/3, 0), "Dec", "Apr", "Jun", "Sep")

Upvotes: 1

Abe Gold
Abe Gold

Reputation: 2347

Or in one line:

GetQuarter = Format(Int((Month(now) - 1) / 3) * 3 & "/1/2014", "mmmm")

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

In a worksheet cell, if A1 contains a date then:

=IF(MONTH(A1)<4,"December",IF(MONTH(A1)<7,"March",IF(MONTH(A1)<10,"June","September")))

EDIT

In VBA

Sub demo()
Dim d As Date, ary()
ary = Array("December", "December", "December", _
        "March", "March", "March", _
        "June", "June", "June", _
        "September", "September", "September")
d = Date
m = ary(Month(d) - 1)
MsgBox m
End Sub

Upvotes: 1

Related Questions