looking for help with a formula...

[fingermouse]

Hi,

I'm sure excel can do this but not sure where to start.

Please refer to the table below. Basically, I need a formula in the 'qtr' column which will reset to 'Q1' any time the value of the reference number in the 'ref' column changes.

Any help much appreciated! Thanks, Cal.

Ref	Start date	End Date	Qtr
0001	26/10/2015	25/01/2017	Q1
0001	26/01/2016 <tbody> </tbody><colgroup><col></colgroup>	25/04/2017	Q2
0001	26/04/2016 <tbody> </tbody><colgroup><col></colgroup>	25/07/2017	Q3
0001	26/07/2016 <tbody> </tbody><colgroup><col></colgroup>	25/10/2017	Q4
0001	26/10/2015	25/01/2016	Q1
0001	26/01/2016 <tbody> </tbody><colgroup><col></colgroup>	25/04/2016	Q2
0002	30/10/2016	29/01/2017	Q1
0002	30/01/2017	29/04/2017	Q2
0002	30/04/2017	29/07/2017	Q3
0003	26/06/2015	27/09/2015	Q1
0003	28/09/2015	27/12/2015	Q2
0003	28/12/2015	27/03/2016	Q3
0003	28/03/2016	27/05/2016	Q4
0004	20/01/2015	19/04/2015	Q1

<tbody>
</tbody>

 

[FormR]

Hi, here is one option you can try:

Excel 2013/2016
	A	B	C	D
1	Ref	Start date	End Date	Qtr
2	0001	26/10/2015	25/01/2017	Q1
3	0001	26/01/2016	25/04/2017	Q2
4	0001	26/04/2016	25/07/2017	Q3
5	0001	26/07/2016	25/10/2017	Q4
6	0001	26/10/2015	25/01/2016	Q1
7	0001	26/01/2016	25/04/2016	Q2
8	0002	30/10/2016	29/01/2017	Q1
9	0002	30/01/2017	29/04/2017	Q2
10	0002	30/04/2017	29/07/2017	Q3
11	0003	26/06/2015	27/09/2015	Q1
12	0003	28/09/2015	27/12/2015	Q2
13	0003	28/12/2015	27/03/2016	Q3
14	0003	28/03/2016	27/05/2016	Q4
15	0004	20/01/2015	19/04/2015	Q1
Sheet1

Cell Formulas
Range	Formula
D2	=IF(A2<>A1,"Q1","Q"&IF(RIGHT(D1)="4","1",RIGHT(D1)+1))

 

[Vinoth_Shob]

Please use this formula - CONCATENATE("Q" & ROUNDUP(MONTH(C2)/3,0))

Column C2 refers to end month date

Hi,

I'm sure excel can do this but not sure where to start.

Please refer to the table below. Basically, I need a formula in the 'qtr' column which will reset to 'Q1' any time the value of the reference number in the 'ref' column changes.

Any help much appreciated! Thanks, Cal.

Ref	Start date	End Date	Qtr
0001	26/10/2015	25/01/2017	Q1
0001	26/01/2016 <tbody> </tbody>	25/04/2017	Q2
0001	26/04/2016 <tbody> </tbody>	25/07/2017	Q3
0001	26/07/2016 <tbody> </tbody>	25/10/2017	Q4
0001	26/10/2015	25/01/2016	Q1
0001	26/01/2016 <tbody> </tbody>	25/04/2016	Q2
0002	30/10/2016	29/01/2017	Q1
0002	30/01/2017	29/04/2017	Q2
0002	30/04/2017	29/07/2017	Q3
0003	26/06/2015	27/09/2015	Q1
0003	28/09/2015	27/12/2015	Q2
0003	28/12/2015	27/03/2016	Q3
0003	28/03/2016	27/05/2016	Q4
0004	20/01/2015	19/04/2015	Q1

<tbody>
</tbody>