software

Hi,

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

=IF(ROUNDUP(MONTH(G2)/3,0)=1,quot;1st Quarter
quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,quot;2nd Quarter
quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,quot;3rd Quarter
quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,quot;4th Quarter quot;amp;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

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),lt;lt;put your table array here gt;gt;,2)

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

Hansquot;RGBquot; wrote:

gt; Hi,
gt;
gt; I have the following (complex!!) formula to work out quarters from dates;
gt;
gt; =IF(ROUNDUP(MONTH(G2)/3,0)=1,quot;1st Quarter
gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,quot;2nd Quarter
gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,quot;3rd Quarter
gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,quot;4th Quarter quot;amp;YEAR(G2)))))
gt;
gt; My question is - how can I change it so that Q1 begins on the 1 November
gt; (the begining of my corps financial year), and not the begining of the
gt; calender year (jan 01)?
gt;
gt; Many thanks
gt;
gt; RB

=CHOOSE(INT(MOD(MONTH(A1)-11,12)/3) 1,quot;1stquot;,quot;2ndquot;,quot;3rdquot;,quot;4thquot;)amp;quot; Quarter
quot;amp;YEAR(A1)

HTH
--
AP

quot;RGBquot; gt; a écrit dans le message de news:
...
gt; Hi,
gt;
gt; I have the following (complex!!) formula to work out quarters from dates;
gt;
gt; =IF(ROUNDUP(MONTH(G2)/3,0)=1,quot;1st Quarter
gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,quot;2nd Quarter
gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,quot;3rd Quarter
gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,quot;4th Quarter quot;amp;YEAR(G2)))))
gt;
gt; My question is - how can I change it so that Q1 begins on the 1 November
gt; (the begining of my corps financial year), and not the begining of the
gt; calender year (jan 01)?
gt;
gt; Many thanks
gt;
gt; RB
I may have overcomplicated it, but I think this works as expected:
=ROUNDUP(MONTH(DATE(2000,MONTH(A1) 1,1))/3,0) amp;
CHOOSE(ROUNDUP(MONTH(DATE(2000,MONTH(A1) 1,1))/3,0),quot;stquot;,quot;ndquot;,quot;rdquot;,quot;thquot;) amp; quot;
Quarter quot; amp; 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.

quot;RGBquot; wrote:

gt; Hi,
gt;
gt; I have the following (complex!!) formula to work out quarters from dates;
gt;
gt; =IF(ROUNDUP(MONTH(G2)/3,0)=1,quot;1st Quarter
gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,quot;2nd Quarter
gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,quot;3rd Quarter
gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,quot;4th Quarter quot;amp;YEAR(G2)))))
gt;
gt; My question is - how can I change it so that Q1 begins on the 1 November
gt; (the begining of my corps financial year), and not the begining of the
gt; calender year (jan 01)?
gt;
gt; Many thanks
gt;
gt; RB

Thanks that works great - one problem though. November amp; 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

quot;Miguel Zapicoquot; wrote:

gt; I may have overcomplicated it, but I think this works as expected:
gt; =ROUNDUP(MONTH(DATE(2000,MONTH(A1) 1,1))/3,0) amp;
gt; CHOOSE(ROUNDUP(MONTH(DATE(2000,MONTH(A1) 1,1))/3,0),quot;stquot;,quot;ndquot;,quot;rdquot;,quot;thquot;) amp; quot;
gt; Quarter quot; amp; YEAR(A1) INT(MONTH(A1)/12)
gt; If you don't want the number suffix the formula is a little less complex. I
gt; have also added the final part to put december in the right year.
gt;
gt; Hope this helps,
gt; Miguel.
gt;
gt; quot;RGBquot; wrote:
gt;
gt; gt; Hi,
gt; gt;
gt; gt; I have the following (complex!!) formula to work out quarters from dates;
gt; gt;
gt; gt; =IF(ROUNDUP(MONTH(G2)/3,0)=1,quot;1st Quarter
gt; gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,quot;2nd Quarter
gt; gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,quot;3rd Quarter
gt; gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,quot;4th Quarter quot;amp;YEAR(G2)))))
gt; gt;
gt; gt; My question is - how can I change it so that Q1 begins on the 1 November
gt; gt; (the begining of my corps financial year), and not the begining of the
gt; gt; calender year (jan 01)?
gt; gt;
gt; gt; Many thanks
gt; gt;
gt; gt; RB

My formula does that!

Cheers,
--
AP

quot;RGBquot; gt; a écrit dans le message de news:
...
gt; Thanks that works great - one problem though. November amp; December of each
gt; calender year are actually in the next Finacial year. E.g. The calender
gt; date
gt; November 1st 2007 is actually in Q1 2008.
gt;
gt; How can I ammend the formula to reflect this?
gt;
gt; Many Thanks
gt;
gt; RB
gt;
gt; quot;Miguel Zapicoquot; wrote:
gt;
gt;gt; I may have overcomplicated it, but I think this works as expected:
gt;gt; =ROUNDUP(MONTH(DATE(2000,MONTH(A1) 1,1))/3,0) amp;
gt;gt; CHOOSE(ROUNDUP(MONTH(DATE(2000,MONTH(A1) 1,1))/3,0),quot;stquot;,quot;ndquot;,quot;rdquot;,quot;thquot;)
gt;gt; amp; quot;
gt;gt; Quarter quot; amp; YEAR(A1) INT(MONTH(A1)/12)
gt;gt; If you don't want the number suffix the formula is a little less complex.
gt;gt; I
gt;gt; have also added the final part to put december in the right year.
gt;gt;
gt;gt; Hope this helps,
gt;gt; Miguel.
gt;gt;
gt;gt; quot;RGBquot; wrote:
gt;gt;
gt;gt; gt; Hi,
gt;gt; gt;
gt;gt; gt; I have the following (complex!!) formula to work out quarters from
gt;gt; gt; dates;
gt;gt; gt;
gt;gt; gt; =IF(ROUNDUP(MONTH(G2)/3,0)=1,quot;1st Quarter
gt;gt; gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,quot;2nd Quarter
gt;gt; gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,quot;3rd Quarter
gt;gt; gt; quot;amp;YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,quot;4th Quarter quot;amp;YEAR(G2)))))
gt;gt; gt;
gt;gt; gt; My question is - how can I change it so that Q1 begins on the 1
gt;gt; gt; November
gt;gt; gt; (the begining of my corps financial year), and not the begining of the
gt;gt; gt; calender year (jan 01)?
gt;gt; gt;
gt;gt; gt; Many thanks
gt;gt; gt;
gt;gt; gt; RB