Auto Categorization

allen121

Board Regular
Joined
May 7, 2003
Messages
60
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

sneel3

Active Member
Joined
Oct 9, 2002
Messages
331
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

Board Regular
Joined
May 7, 2003
Messages
60
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Try this formula and copy it down.

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

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

or, for the sake of variety:

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
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.:eek:

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"I'll go back into my little VBA hole now I think."

Happy digging :)
 

Forum statistics

Threads
1,141,630
Messages
5,707,511
Members
421,511
Latest member
mgroah1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top