formula to return a year based on a date range

Hi

I want to return a year based on a date that is in cell A2. The criteria is below. Once the formula which would be in b2 is correct I would copy it down to the other rows so that a year is returned based on the date in the cells in colum A.

>30/06/2008 but <01/07/2009 = 2009
>30/06/2009 but <01/07/2010 = 2010
>30/06/2010 but <01/07/2011 = 2011
>30/06/2011 but <01/07/2012 = 2012

Any Help is much appreciated

Thanks

30/06/2008 but <01/07/2009 = 2009
I dont understand how 2009 is the answer. Are you just wanting to return the year of the date on the right?

Hi,

No i want to return a year based on the range of dates. The returned year is the financial year end. It may not be the actual year of the value in cell A2...does that make sense...sorry for confusing

So which ever year has the 1st of April in you want to return? Is that your financial year end? or is 1st of July your financial year end?

our financial year ends on 30th of June. So 30/06/08 would be 2008, 30/06/09 would be 2009 etc...

Thanks

Originally Posted by darknation144

=IFERROR(IF(MONTH(A9)>=7,YEAR(A9),IF(MONTH(A9)<6,(YEAR(A9)+1),(IF(DAY(A9)<=30,YEAR(A9))))),"Incorrect Date Entered")

Try that

I think we are getting there and you have pointed me in the right direction. I have teaked it slightly as the result returned was out. I have tried this against various dates and it seems to pull through the correct year.

=IF(MONTH(A1)>=7,YEAR(A1)+1,IF(MONTH(A1)<7,YEAR(A1),(IF(DAY(A1)<=30,YEAR(A1-1)))))

I will do a bit more testing but if you see a problem with what I have done can you let me know please

Thanks again

Yours seems to work sorry mine had a mistake in it. Glad I could help.

I have no idea how that works but wow impressive.