Group data by UK financial years
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 |
Group data by UK financial years
Tony,
Take a look at the function SumProduct(). It will do the job ... HTH Carim |
Group data by UK financial years
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 |
Group data by UK financial years
Tony,
Yes. You are right. My understanding was that you had already 3 columns : Beg Date, End Date and Amount ... Cheers Carim |
Group data by UK financial years
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 |
All times are GMT +1. The time now is 12:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com