All year divided in 4 quarter as given below

1st quarter is from 28/12/08 to 28/03/09 (13 week)
2nd quarter is from 29/03/09 to 27/06/09 (13 week)
3rd quarter is from 28/06/09 to 26/09/09 (13 week)
4th quarter is from 27/09/09 to 02/01/10 (14 week)

If A column has any date then I want a formula for number of quarter in
column B
For Example
A B
05/01/2009 1
20/03/2009 1
30/03/2009 2
05/06/2009 2
15/07/2009 3
11/09/2009 3
02/10/2009 4
27/12/2009 4

On Mon, 11 May 2009 03:06:42 -0700, kashish
wrote:

All year divided in 4 quarter as given below

1st quarter is from 28/12/08 to 28/03/09 (13 week)
2nd quarter is from 29/03/09 to 27/06/09 (13 week)
3rd quarter is from 28/06/09 to 26/09/09 (13 week)
4th quarter is from 27/09/09 to 02/01/10 (14 week)

If A column has any date then I want a formula for number of quarter in
column B
For Example
A B
05/01/2009 1
20/03/2009 1
30/03/2009 2
05/06/2009 2
15/07/2009 3
11/09/2009 3
02/10/2009 4
27/12/2009 4

Here's one method. But it will only work for the range of dates listed.

Place the quarter-starting-date in D1:D4. Then you can use this formula:

=IF(OR(A1<$D$1,A1$D$4+14*7),"Date out of range",MATCH(A1,$D$1:$D$4))

If you have a method for precisely defining the quarters, that could be
incorporated into the formula.
--ron

On Mon, 11 May 2009 03:06:42 -0700, kashish
wrote:

All year divided in 4 quarter as given below

1st quarter is from 28/12/08 to 28/03/09 (13 week)
2nd quarter is from 29/03/09 to 27/06/09 (13 week)
3rd quarter is from 28/06/09 to 26/09/09 (13 week)
4th quarter is from 27/09/09 to 02/01/10 (14 week)

If A column has any date then I want a formula for number of quarter in
column B
For Example
A B
05/01/2009 1
20/03/2009 1
30/03/2009 2
05/06/2009 2
15/07/2009 3
11/09/2009 3
02/10/2009 4
27/12/2009 4

In trying to determine how your quarters are calculated, I note a problem with
your quarter definitions. I should have noticed this before.

Your quarter ending dates are listed as being in both the current and the next
quarter! You need to correct this.

Perhaps you are using ISO weeknumbers to define your quarters?
--ron

"kashish" wrote:
1st quarter is from 28/12/08 to 28/03/09 (13 week)
2nd quarter is from 29/03/09 to 27/06/09 (13 week)
3rd quarter is from 28/06/09 to 26/09/09 (13 week)
4th quarter is from 27/09/09 to 02/01/10 (14 week)

I am quite certain the 4th quarter is 27/09/09 to 27/12/09. Otherwise,
28/12/09 would fit into 08Q4 as well as 09Q1.

Put the following dates in some out-of-the-way column, say X1:X5:

1/1/2008
29/3/2008
28/6/2008
27/9/2008
28/12/2008

Then in B1 and copy down:

=lookup(date(2008,month(A1),day(A1)),X1:X5,{1,2,3, 4,1})

Note that this will work for a date in any year. The choice of
DATE(2008,...) has nothing to do with the fact that your dates start in
2008. It is chosen because it is a leap year; so Feb 29 is handled
correctly. (It would have been anyway, since the 2nd quarter does not start
until Mar 29. But we have to pick __some__ year; might as well handle Feb
correctly.)


----- original message -----

"kashish" wrote in message
...
All year divided in 4 quarter as given below

1st quarter is from 28/12/08 to 28/03/09 (13 week)
2nd quarter is from 29/03/09 to 27/06/09 (13 week)
3rd quarter is from 28/06/09 to 26/09/09 (13 week)
4th quarter is from 27/09/09 to 02/01/10 (14 week)

If A column has any date then I want a formula for number of quarter in
column B
For Example
A B
05/01/2009 1
20/03/2009 1
30/03/2009 2
05/06/2009 2
15/07/2009 3
11/09/2009 3
02/10/2009 4
27/12/2009 4

Hi,

Try this. In range C5:C8, type 28/12/2008,29/03/2009,28/06/2009,27/09/2009.
In range D5:D8, type 1,2,3,4. Enter a date in cell C11 and in cell D11, use
the following formula =VLOOKUP(C11,$C$5:$D$8,2).

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"kashish" wrote in message
...
All year divided in 4 quarter as given below

1st quarter is from 28/12/08 to 28/03/09 (13 week)
2nd quarter is from 29/03/09 to 27/06/09 (13 week)
3rd quarter is from 28/06/09 to 26/09/09 (13 week)
4th quarter is from 27/09/09 to 02/01/10 (14 week)

If A column has any date then I want a formula for number of quarter in
column B
For Example
A B
05/01/2009 1
20/03/2009 1
30/03/2009 2
05/06/2009 2
15/07/2009 3
11/09/2009 3
02/10/2009 4
27/12/2009 4

A different approach...

A1 = date

=IF(OR(A1<=DATE(2008,12,27),A1=DATE(2010,1,2)),"" ,LOOKUP(VALUE(TEXT(A1,"yyyymmdd")),{20081228,20090 328,20090627,20090926},{1,2,3,4}))


If this post helps click Yes
---------------
Jacob Skaria


"kashish" wrote:

All year divided in 4 quarter as given below

1st quarter is from 28/12/08 to 28/03/09 (13 week)
2nd quarter is from 29/03/09 to 27/06/09 (13 week)
3rd quarter is from 28/06/09 to 26/09/09 (13 week)
4th quarter is from 27/09/09 to 02/01/10 (14 week)

If A column has any date then I want a formula for number of quarter in
column B
For Example
A B
05/01/2009 1
20/03/2009 1
30/03/2009 2
05/06/2009 2
15/07/2009 3
11/09/2009 3
02/10/2009 4
27/12/2009 4

Oops... Same as what Ashish proposed...

=LOOKUP(VALUE(TEXT(A1,"yyyymmdd")),{20081227,20081 228,20090328,20090627,20090926,20100103},{"",1,2,3 ,4,""})
--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

A different approach...

A1 = date

=IF(OR(A1<=DATE(2008,12,27),A1=DATE(2010,1,2)),"" ,LOOKUP(VALUE(TEXT(A1,"yyyymmdd")),{20081228,20090 328,20090627,20090926},{1,2,3,4}))


If this post helps click Yes
---------------
Jacob Skaria


"kashish" wrote:

All year divided in 4 quarter as given below

1st quarter is from 28/12/08 to 28/03/09 (13 week)
2nd quarter is from 29/03/09 to 27/06/09 (13 week)
3rd quarter is from 28/06/09 to 26/09/09 (13 week)
4th quarter is from 27/09/09 to 02/01/10 (14 week)

If A column has any date then I want a formula for number of quarter in
column B
For Example
A B
05/01/2009 1
20/03/2009 1
30/03/2009 2
05/06/2009 2
15/07/2009 3
11/09/2009 3
02/10/2009 4
27/12/2009 4

"Ashish Mathur" wrote...
Try this. *In range C5:C8, type 28/12/2008,29/03/2009,28/06/2009,27/09/2009.
In range D5:D8, type 1,2,3,4. *Enter a date in cell C11 and in cell D11, use
the following formula =VLOOKUP(C11,$C$5:$D$8,2).

The second column is unnecessary.

=MATCH(C11,$C$5:$C$8)

would be sufficient. Actually, the C5:C8 range is unnecessary.

=MATCH(C11,--{"2008-12-28";"2009-03-29";"2009-06-28";"2009-09-27"})

would return the same results.

On Mon, 11 May 2009 06:28:06 -0400, Ron Rosenfeld
wrote:

In trying to determine how your quarters are calculated, I note a problem with
your quarter definitions. I should have noticed this before.

Your quarter ending dates are listed as being in both the current and the next
quarter! You need to correct this.

Perhaps you are using ISO weeknumbers to define your quarters?
--ron


Forget that. I was looking at something else.
--ron