Putting dates into quarters for quarterly report.

[bjf44]

How do I seperate multiple dates into quarters?

In EXCEL 2007 I have > 33000 rows of dated data.
The dates are unsorted & run from 1/1/2012 to 12/31/2012.

I have used the month() formula to seperate these in monthly categories of 1 - 12 & then put the data to a pivot tbl and pivot chart.

I would like to view the data in 4 quartiles to give me more user freindly pivot charts.

How do I change a date to a quartile using a formula?

I am a COBOL programmer and while I would use a nested if or a case statement I am without VB macro skills.

Any instruction is appreciated.

Thanks.

 

[JoeMo]

If you are using calendar quarters and have a date in A2 then in B2:

=IF(AND(MONTH(A2)>=1,MONTH(A2)<=3),1,IF(AND(MONTH(A2)>=4,MONTH(A2)<=6),2,IF(AND(MONTH(A2)>=7,MONTH(A2)<=9),3,4)))

Returns the quarter number for that date.

 

[bjf44]

Joe,

I see how you have combined the logical fx's to get a very useful nested IF.

It works great in my spread sheet.

I appreciate the quote also.

Thanks,

Bruce

 

[JoeMo]

Joe,

I see how you have combined the logical fx's to get a very useful nested IF.

It works great in my spread sheet.

I appreciate the quote also.

Thanks,

Bruce

Bruce,
Glad to help. Thanks for the feedback.

 

[WLHagen]

found this in an Excel book - should also work:

assuming that your date is in A2:

=Roundup(Month(A2)/3,0)

 

[bjf44]

WLHagan,

=Roundup(Month(A2)/3,0) works well also.

Thanks for the help.

Bruce