Setting quarter dates in excel
 

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

Setting quarter dates in excel
 

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


"RGB" wrote:

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

Setting quarter dates in excel
 

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

HTH
--
AP

"RGB" a écrit dans le message de news:
...
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

Setting quarter dates in excel
 

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.

"RGB" wrote:

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

Setting quarter dates in excel
 

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

"Miguel Zapico" wrote:

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.

"RGB" wrote:

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

Setting quarter dates in excel
 

My formula does that!

Cheers,
--
AP

"RGB" a écrit dans le message de news:
...
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

"Miguel Zapico" wrote:

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.

"RGB" wrote:

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