Group data by UK financial years

[annysjunkmail]

Hi Group,

I have hundreds of rows of data.
I would like to group the data by UK financial year (01 Apil - 31 Mar)
to obtain totals by releavnt years.

Can someone suggest a way forward? I have tried pivot tables but Excel
says it cannot group on that data. Ideally, I would like to end up
with something like the following:

Year Total Payments
01/04/2002 - 31/03/2003 £79,557.25
01/04/2003 - 31/03/2004 £244,728.69
01/04/2004 - 31/03/2005 £450,063.19
01/04/2005 - 31/03/2006 £5,000.00

Many thanks
Tony

 

[Carim]

Tony,

Take a look at the function SumProduct().
It will do the job ...
HTH

Carim

 

[annysjunkmail]

Carim,

Not so sure.
Dates are spread across 10 different years, with different dates and
months etc.
WOuld I not need a function to group the financial year first and then
use sumproduct form there? I'm not an expert but after reading
previous posts grouping dates by financial years seems pretty daunting

Thanks
Tony

 

[Carim]

Tony,

Yes. You are right. My understanding was that you had already 3 columns
:
Beg Date, End Date and Amount ...

Cheers
Carim

 

[Carim]

Tony,

As far Fiscal Years are concerned, you could adapt
your own UDF based on the following one :

Function FY(dDate As Date) As String

Select Case Month(dDate)

Case 6, 7, 8: FY = "Q1 " & "FY" & Year(dDate) + 1

Case 9, 10, 11: FY = "Q2 " & "FY" & Year(dDate) + 1

Case 12: FY = "Q3 " & "FY" & Year(dDate) + 1

Case 1, 2: FY = "Q3 " & "FY" & Year(dDate)

Case 3, 4, 5: FY = "Q4 " & "FY" & Year(dDate)

End Select
End Function

Hope this clarifies

Cheers
Carim