CODEWITHSUNDEEP
vbadatems-access
user2294977
user2294977

Reputation: 111

Access VBA Get Quarter From Month Number

I am stuck in trying to find the quarter of year number from the month number i.e. i have month number 2 which should be quarter number 1, how do i do this in Access VBA or an access query? Thanks in advance.

Upvotes: 5

Views: 15552

Answers (4)

Jon
Jon

Reputation: 1

The format function will convert a date to the calendar quarter:

In string format: Quarter = Format((Date), "Q")

or use Val to convert to number format: Quarter = Val(Format((Date), "Q"))

Upvotes: 0

Ian M
Ian M

Reputation: 26

In Excel VBA I like using Choose as sometimes I need to get the financial quarter rather than the calendar quarter.

calendar_quarter = Choose(Month(Date), 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4)

' if financial year starts in April - this will get the financial quarter from the current date
financial_quarter = Choose(Month(Date), 4, 4, 4, 1, 1, 1, 2, 2, 2, 3, 3, 3)

Upvotes: 1

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

One liner for Excel:

=INT((MonthNo-1)/3 +1)

for SQL

floor(([MonthNo]-1)/3) +1

Upvotes: 0

user2271770
user2271770

Reputation:

You can use this function:

Public Function Quarter(ByVal MonthNumber As Long) As Long

        If (MonthNumber < 1) Or (MonthNumber > 12) Then
                Call Err.Raise(6) ' Value out of Bounds '
        End If

        Let Quarter = (MonthNumber - 1) \ 3 + 1

End Function

Upvotes: 10

Related Questions