Setting quarter dates in excel

[Guest]

Hi,

I have the following (complex!!) formula to work out quarters from dates;

=IF(ROUNDUP(MONTH(G2)/3,0)=1,"1st Quarter
"&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,"2nd Quarter
"&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,"3rd Quarter
"&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,"4th Quarter "&YEAR(G2)))))

My question is - how can I change it so that Q1 begins on the 1 November
(the begining of my corps financial year), and not the begining of the
calender year (jan 01)?

Many thanks

RB

 

[Guest]

Why don't you use a lookup table to identify the Quarter.
Insert a sheet in your workbook with the folowing data

11 Q1
12 Q1
1 Q1
2 Q2
etc.

Then use a formula like =vlookup(month(G2),<<put your table array here >>,2)

This should retur the quarter and it makes your formula a bit simpler.

Hans

 

[Ardus Petus]

=CHOOSE(INT(MOD(MONTH(A1)-11,12)/3)+1,"1st","2nd","3rd","4th")&" Quarter
"&YEAR(A1)

HTH

 

[Guest]

I may have overcomplicated it, but I think this works as expected:
=ROUNDUP(MONTH(DATE(2000,MONTH(A1)+1,1))/3,0) &
CHOOSE(ROUNDUP(MONTH(DATE(2000,MONTH(A1)+1,1))/3,0),"st","nd","rd","th") & "
Quarter " & YEAR(A1) + INT(MONTH(A1)/12)
If you don't want the number suffix the formula is a little less complex. I
have also added the final part to put december in the right year.

Hope this helps,
Miguel.

 

[Guest]

Thanks that works great - one problem though. November & December of each
calender year are actually in the next Finacial year. E.g. The calender date
November 1st 2007 is actually in Q1 2008.

How can I ammend the formula to reflect this?

Many Thanks

RB

 

[Ardus Petus]

My formula does that!

Cheers,
--
AP

Thanks that works great - one problem though. November & December of each
calender year are actually in the next Finacial year. E.g. The calender
date
November 1st 2007 is actually in Q1 2008.

How can I ammend the formula to reflect this?

Many Thanks

RB