Determine financial year end for a date

Financial_Yearend(dDate As Date, nMonth As Integer) As Date
Dim Ye as Date
Ye = Date(year(dDate), nmonth +1, 1) -1
If (Ye < dDate) then
     Financial_Yearend = Date(year(Ye)+1,month(Ye),Day(Ye))
Else
     Financial_Yearend = Ye
End if
End Function

Written without Excel to hand so there may be some errors

pjwhitfield:

Should the date function you used be dateserial function (if I use date as you had it it gives an error)? If so then it doesn't work. See YE returned below. It's not handling the leap year correctly.
Date: 01/01/2007 FY: 28/02/2007 - correct
Date: 30/01/2007 FY: 28/02/2007 - correct
Date: 28/02/2007 FY: 28/02/2007 - correct
Date: 29/03/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 27/04/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 26/05/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 24/06/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 23/07/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 21/08/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 19/09/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 18/10/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 16/11/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 15/12/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 13/01/2008 FY: 29/02/2008 - here it's correct
Date: 11/02/2008 FY: 29/02/2008 - here it's correct
Date: 11/03/2008 FY: 01/03/2009 - this should be 28/02/2009
Date: 09/04/2008 FY: 01/03/2009 - this should be 28/02/2009
Date: 08/05/2008 FY: 01/03/2009 - this should be 28/02/2009
Date: 06/06/2008 FY: 01/03/2009 - this should be 28/02/2009
Date: 05/07/2008 FY: 01/03/2009 - this should be 28/02/2009

Schoolboy error on my behalf!

To get the last day of month I was setting the date to the 1st of the following month and deducting a day.....which works fine however I was then deciding if it needed to be the year after and adding a year on rather than doing it the other way round!

Function Financial_Yearend(dDate As Date, nMonth As Integer) As Date
Dim Ye As Date
Ye = DateSerial(Year(dDate), nMonth + 1, 1)
If (Ye < dDate) Then
     Financial_Yearend = DateSerial(Year(Ye) + 1, Month(Ye), Day(Ye))
Else
     Financial_Yearend = Ye
End If
     Financial_Yearend = Financial_Yearend - 1
End Function

How about just a formula?

Row\Col	H	I	J	K
1	Date	Month	EoFY
2	02/28/2014	2	02/28/2014	J2: =EOMONTH(DATE(YEAR(H2) + (MONTH(H2) > I2), I2, 1), 0)
3	03/01/2014	2	02/28/2015

Another way:

Function EoFY(tDate As Date, iMon As Long) As Date
  EoFY = DateSerial(Year(tDate) - (Month(tDate) > iMon), iMon + 1, 0)
End Function