year quarters === Financial Year
 

I group months by Quarter, but I need to group the Quarter by Financial Year
Quarter Finance year
ie - 2007Q2 = 2007-08
2008Q1 = 2007-08
2008Q2 = 2008-09

I then carry out a SUMPRODUCT to pick up values which involve looking up
quarter values

I need to maintain the form as I have used this elsewhere in my workbook &
don't want to change anything

Thanks in advance

Saintsman

year quarters === Financial Year
 

With "2007Q1" in A2, this formula will convert it into the appropriate
financial year:

=IF(RIGHT(A2,1)="1",LEFT(A2,4)-1&"-"&MID(A2,3,2),LEFT(A2,4)&"-"&TEXT(MID(A2,3,2)+1,"00"))


Hope this helps.

Pete

On Sep 11, 4:44 pm, Saintsman
wrote:
I group months by Quarter, but I need to group the Quarter by Financial Year
Quarter Finance year
ie - 2007Q2 = 2007-08
2008Q1 = 2007-08
2008Q2 = 2008-09

I then carry out a SUMPRODUCT to pick up values which involve looking up
quarter values

I need to maintain the form as I have used this elsewhere in my workbook &
don't want to change anything

Thanks in advance

Saintsman

year quarters === Financial Year
 

Briiliant
Thanks very much indeed

"Pete_UK" wrote:

With "2007Q1" in A2, this formula will convert it into the appropriate
financial year:

=IF(RIGHT(A2,1)="1",LEFT(A2,4)-1&"-"&MID(A2,3,2),LEFT(A2,4)&"-"&TEXT(MID(A2,3,2)+1,"00"))


Hope this helps.

Pete

On Sep 11, 4:44 pm, Saintsman
wrote:
I group months by Quarter, but I need to group the Quarter by Financial Year
Quarter Finance year
ie - 2007Q2 = 2007-08
2008Q1 = 2007-08
2008Q2 = 2008-09

I then carry out a SUMPRODUCT to pick up values which involve looking up
quarter values

I need to maintain the form as I have used this elsewhere in my workbook &
don't want to change anything

Thanks in advance

Saintsman

year quarters === Financial Year
 

You're welcome - thanks for feeding back.

Pete

On Sep 12, 11:50 am, Saintsman
wrote:
Briiliant
Thanks very much indeed



"Pete_UK" wrote:
With "2007Q1" in A2, this formula will convert it into the appropriate
financial year:

=IF(RIGHT(A2,1)="1",LEFT(A2,4)-1&"-"&MID(A2,3,2),LEFT(A2,4)&"-"&TEXT(MID(A2*,3,2)+1,"00"))

Hope this helps.

Pete

On Sep 11, 4:44 pm, Saintsman
wrote:
I group months by Quarter, but I need to group the Quarter by Financial Year
Quarter Finance year
ie - 2007Q2 = 2007-08
2008Q1 = 2007-08
2008Q2 = 2008-09

I then carry out a SUMPRODUCT to pick up values which involve looking up
quarter values

I need to maintain the form as I have used this elsewhere in my workbook &
don't want to change anything

Thanks in advance

Saintsman- Hide quoted text -

- Show quoted text -