IF Date issue

[scottydogg84]

I have a spreadsheet which includes thousands of invoices over the past 5 years, with info such as invoice number, date and amount.

What I need is a formula which will output "2006/07" IF the date (in column I) falls between 01/04/2006 and 31/03/2007, "2007/08" IF the date falls between 01/04/2007 and 31/03/2008, "2008/09" IF the date falls between 01/04/2008 and 31/03/2009, "2009/10" IF the date falls between 01/04/2009 and 31/03/2010 AND "2010/11" IF the date falls between 01/04/2010 and 31/03/2011.

I presume this would be some kind of long IF formula but I have a fairly basic knowledge of Excel so apologize in incorrect.

Thanking you advance.

 

[Trevor G]

Just to get you going, have you considered using a filter and select between dates? Then you could copy the results to another sheet, then repeat for each period.

 

[scottydogg84]

Yes but this doesn't really help me in what i want to achieve.

I want to return this data so i can then create a pivot table which will look like this:

Billings
Client 2006/07 2007/08 2008/09 2009/10 2010/11
Smith £80,000 £1,000 £500 0 0
Jones £85,000 £10,000 £600 £50 £6,000
Harold £60,000 £15,000 £5,000 £40 £10,000

If you could help with this formula i would be most grateful.

 

[Trevor G]

This formula will work out the first years which will get you going.

Excel Workbook
	I	J
1	Date	Result
2	01/05/2006	2006/07
3	12/12/2007
4	01/03/2007	2006/07
5	30/10/2008
6	14/09/2009
7	12/01/2011
Sheet1

 

[scottydogg84]

Cheers Trevor. Worked well.

Just out of interest for speed purposes, is there such a formula that could return any of the required data without me having to amend the formula for each year? Just have one formula that could be copied down the spreadsheet returning 2007/08 or 2008/09 etc without me having to change anything?

Sorry, i know i'm being lazy but would be handy to know as i could use this sort of formula for lot's of other things.

Thanks again.

 

[Trevor G]

You are limited by the amount of IFs you can use. Here is some VBA code that may well help you. I have tested on the same amount of data I used with the Formula and it works quickly.

Sub FinYr()
With Range("i2", Range("i" & Rows.Count).End(xlUp)).Offset(, 1)
.NumberFormat = "General"
.Formula = "=RIGHT(YEAR(i2)-(MONTH(i2)<4),2)&""/""" _
& "&RIGHT(YEAR(i2)+(MONTH(i2)>3),2)"
.NumberFormat = "@"
.Value = .Value
End With
End Sub

Results show the following:

Excel Workbook
	I	J
1	Date	Result
2	01/05/2006	06/07
3	12/12/2007	07/08
4	01/03/2007	06/07
5	30/10/2008	08/09
6	14/09/2009	09/10
7	12/01/2011	10/11
Sheet1