Excel 2007 Quarter and Year

[lj]

What is the formula to show a quarter and year if you have a date like
7/1/2009?

 

[JoeU2004]

What is the formula to show a quarter and year
if you have a date like 7/1/2009?

Depends on how you define a quarter, and the form that you are looking for
(i.e. 1Q09, 1Q2009, among others).

If you mean a calendar quarter and the date is A1, then try one of the
following:

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

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

Format with right Horizontal Alignment, if that's what you want.

 

[Dave Peterson]

I use this formula to show the fiscal year and quarter:

="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3)

So if the fiscal year starts on Oct 1st, then I'd use:
="FY"&YEAR(A1)-(MONTH(A1)<10)&"-Q"&INT(1+MOD(MONTH(A1)-10,12)/3)

It results in an expression like:
FY2009-Q1

It makes sorting easier.

If you don't care about the quarter, you an drop that stuff:
="FY"&YEAR(A1)-(MONTH(A1)<10)

 

[lisaj]

Depends on how you define a quarter, and the form that you are looking for
(i.e. 1Q09, 1Q2009, among others).

If you mean a calendar quarter and the date is A1, then try one of the
following:

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

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

Format with right Horizontal Alignment, if that's what you want.

Excellent, Thanks guys!

 

[Shane Devenshire]

Hi,

What formula you use depends on how you want to display the results. Try this

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

The CHOOSE example given on the referenced website is rather long