Calculation of Quarter

  • Thread starter Thread starter kashish
  • Start date Start date
K

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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 said:
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)
Click to expand...

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 said:
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)
Click to expand...

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 -----
 
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
 
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
 
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
 
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
 
Oops... Same as what Ashish proposed...

=LOOKUP(VALUE(TEXT(A1,"yyyymmdd")),{20081227,20081228,20090328,20090627,20090926,20100103},{"",1,2,3,4,""})
 
Oops... Same as what Ashish proposed...

=LOOKUP(VALUE(TEXT(A1,"yyyymmdd")),{20081227,20081228,20090328,20090627,20090926,20100103},{"",1,2,3,4,""})
 
Ashish Mathur said:
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).
Click to expand...

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.
 
Ashish Mathur said:
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).
Click to expand...

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.
 
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
Click to expand...


Forget that. I was looking at something else.
--ron
 
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
Click to expand...


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