Auto Categorization

[allen121]

Hello,

I have a report which has a column for the date (i.e. 13-Jun-2006) that range from 01-Jan-2006 to today.

I want to be able to automatically categorize each row into which quarter the date falls into. For example, if a date falls between 01-Jan and 31-Mar, it would be labeled as "Q1". I'd like to insert a new column where I would just enter a formula and depending on the date, will automatically insert either Q1, Q2, Q3, or Q4.

Thanks for the help.

 

[sneel3]

create a table on a separate tab that has a list of all dates in one column and the quarter in which each date belongs in the next column, then do a vlookup on that table

 

[allen121]

create a table on a separate tab that has a list of all dates in one column and the quarter in which each date belongs in the next column, then do a vlookup on that table

By doing that, it accomplishes what I am looking for. However, the report has thousands of rows and I would rather use a macro or formula to categorize this for me so I don't have to do any manual work. Thanks.

 

[Norie]

Try this formula and copy it down.

=LOOKUP(MONTH(A1),{0,4,8,12},{"Q1","Q2","Q3","Q4"})

 

[PaddyD]

or, for the sake of variety:

="Q"&ROUNDUP(MONTH(A1)/3,0)

 

[Norie]

Paddy

Tried your formula next to mine and there were some differences.

Mind you that's probably because my formula's wrong in some way.

No suprise there, I'm no formula guru but I thought I'd take a stab at the question anyway.

Perhaps I'll leave it to the big guns.

 

[PaddyD]

yours just needs a slight tweek:

=LOOKUP(MONTH(A1),{1,4,7,10},{"Q1","Q2","Q3","Q4"})

...using the lookup() like that, basic thing to remember is that the lookup value in the lookup table / array needs to be the lowest value for the class.

 

[Norie]

Paddy

Cheers.

I knew it was something like that, still can't get my head around some of the formulas that people come up with, and how they actually work.

I'll go back into my little VBA hole now I think.

 

[PaddyD]

"I'll go back into my little VBA hole now I think."

Happy digging