Log in

View Full Version : [SOLVED:] What Quarter is the Date?



Kindly_Kaela
02-07-2007, 10:22 AM
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
:cloud9:

Ken Puls
02-07-2007, 10:31 AM
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
02-07-2007, 10:58 AM
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
02-07-2007, 11:10 AM
Formula

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

VBA

(Month(the_date) + 2) \ 3

mdmackillop
02-07-2007, 11:14 AM
Just to be different

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

Ken Puls
02-07-2007, 11:22 AM
Nice guys. Bob, you're the king of efficiency. ;)

Bob Phillips
02-07-2007, 11:46 AM
Thank you Ken, think I will go and lie down now to save energy.

Kindly_Kaela
02-07-2007, 11:53 AM
Fantastic replies! Thanks everyone!

malik641
02-07-2007, 12:23 PM
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 :thumb