Assigning Dates into Quarters

[Russell O.]

All help is appreciated...

What is the formula for assigning dates to the correct
calendar quarter?

Ex: 1/1/2004 in Column A Displays Q1 in Column B
11/1/2004 in Column A Displays Q4 in Column B

Etc.

Tried:

=IF(C2>=8/1/2004,"Q4",IF(C2>=5/1/2004,"Q3", IF
(C2>=3/1/2004,"Q2",IF(C2>=11/1/2003,"Q1"))))

This aligns to our firm's fiscal quarters.

Thanks, Russell.

 

[Ron Rosenfeld]

All help is appreciated...

What is the formula for assigning dates to the correct
calendar quarter?

Ex: 1/1/2004 in Column A Displays Q1 in Column B
11/1/2004 in Column A Displays Q4 in Column B

=CEILING(MONTH(A1)/3,1)

and format the cell as \Q0

or =TEXT(CEILING(MONTH(A1)/3,1),"\Q0")

--ron

 

[Ron Rosenfeld]

Tried:

=IF(C2>=8/1/2004,"Q4",IF(C2>=5/1/2004,"Q3", IF
(C2>=3/1/2004,"Q2",IF(C2>=11/1/2003,"Q1"))))

I missed this in my first reply. To align as you indicate, try this formula:

=CEILING(MONTH(DATE(1,MONTH(A1)-10,1))/3,1)

with the cell format as \Q0

or

=TEXT(CEILING(MONTH(DATE(1,MONTH(A1)-10,1))/3,1),"\Q0")


--ron

 

[Ron Rosenfeld]

=IF(C2>=8/1/2004,"Q4",IF(C2>=5/1/2004,"Q3", IF
(C2>=3/1/2004,"Q2",IF(C2>=11/1/2003,"Q1"))))

And a bit simpler:

=CEILING(((MONTH(A1)+2)>12)+MOD(MONTH(A1)+2,13)/3,1)

formatted as \Q0

or

=TEXT(CEILING(((MONTH(A1)+2)>12)+MOD(MONTH(A1)+2,13)/3,1),"\Q0")


--ron

 

[JE McGimpsey]

One way:

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

Format with Format/Cells/Number/Custom \Q0