What Quarter is the Date?

[Kindly_Kaela]

My program needs to figure out which quarter today currently is, along with hundreds of other dates. The contingency is simple; (January, February, and March) = Q1.... (April, May , June = Q2).... etc.

I was hoping for something as simple as; If Month = "February" then Quarter = Q1. I tried using Today() in excel with a customized format of "mmmm". The output in Excel is "February" but VBA still recognizes it as a full date or long number. It does not recognize just "February". So my simple logic will not work.

Any suggestions?

Thanks!
Kaela

[Ken Puls]

Does it need to be VBA?

The following set of native formulas should work (where your date is in A1)

=ROUNDDOWN(MONTH(A1)/(4*10/12),0)+1 

HTH,

[malik641]

I was hoping for something as simple as; If Month = "February" then Quarter = Q1. I tried using Today() in excel with a customized format of "mmmm". The output in Excel is "February" but VBA still recognizes it as a full date or long number. It does not recognize just "February". So my simple logic will not work.

Hey Kaela,

I'm assuming you're reading cells that you custom formated the cell to display the month of Today(). You could do 2 things in this case:

1. If you read Range().Text, you will get the string i.e. "February" and VBA would read it as such. If you use Range() then it would return the date.
2. If you used the formula Text(Today(),"mmmm") VBA would read Range() as "February" as a string because the formula would return a string.

If you want a VBA answer you could use this:

Public Sub Test1()
Select Case Format(Date, "m")
    Case 1 To 3 ' Quarter 1
        Debug.Print "Quarter 1"
    Case 4 To 6 ' Quarter 2
        Debug.Print "Quarter 2"
    Case 7 To 9 ' Quarter 3
        Debug.Print "Quarter 3"
    Case Else
        Debug.Print "Quarter 4"
End Select
End Sub

Hope this helps!

And Ken, nice formula

[Bob Phillips]

Formula

=INT((MONTH(A1)+2)/3) 

VBA

(Month(the_date) + 2) \ 3

[mdmackillop]

Just to be different

=QUOTIENT(MONTH(A1),3.1)+1 

[Ken Puls]

Nice guys. Bob, you're the king of efficiency.

[Bob Phillips]

Thank you Ken, think I will go and lie down now to save energy.

[Kindly_Kaela]

Fantastic replies! Thanks everyone!

[malik641]

VBA

(Month(the_date) + 2) \ 3

Great use of the \ operator. I've never used it before cause I never ran into a case where I thought I'd need it. Nice example, Bob