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
|