Calculation of Quarter

[kashish]

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

 

[Ron Rosenfeld]

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 D14. 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

 

[Ron Rosenfeld]

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

 

[JoeU2004]

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 -----

 

[Ashish Mathur]

Hi,

Try this. In range C5:C8, type 28/12/2008,29/03/2009,28/06/2009,27/09/2009.
In range D58, 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

 

[Jacob Skaria]

A different approach...

A1 = date

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


If this post helps click Yes

 

[Jacob Skaria]

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

=LOOKUP(VALUE(TEXT(A1,"yyyymmdd")),{20081227,20081228,20090328,20090627,20090926,20100103},{"",1,2,3,4,""})

 

[Harlan Grove]

Try this.  In range C5:C8, type 28/12/2008,29/03/2009,28/06/2009,27/09/2009.
In range D58, 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.

 

[Ron Rosenfeld]

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