Your function seems rather inconsistent. Firstly the date ranges overlap on
31 July/1 August; secondly it returns the same value for the two years 31
July 2008 to 1 August 2010. Did you mean to return 2010 in the last case?
For greater flexibility the following returns the accounting year in a format
such as 2009-10 by passing the date value, and the day and month when the
accounting year starts into the function:
Public Function AcctYear(DateVal As Date, MonthStart As Integer, DayStart As
Integer) As String
Dim dtmYearStart As Date
' get start of accounting year in year of date value
dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)
' if date value is before start of accounting year
' accounting year starts year previous to date's year,
' otherwise it starts with date's year
If DateVal < dtmYearStart Then
AcctYear = Year(DateVal) - 1 & Format(Year(DateVal) Mod 100, "-00")
Else
AcctYear = Year(DateVal) & Format((Year(DateVal) + 1) Mod 100, "-00")
End If
End Function
For the UK fiscal year traditionally starting 5 April for instance:
AcctYear([TheDate], 4, 5)
In your case it would be:
AcctYear([TheDate], 8, 1)
I'm assuming your year starts 1 August.
To return just the ending year you'd amend it to:
If DateVal < dtmYearStart Then
AcctYear = Year(DateVal)
Else
AcctYear = Year(DateVal) + 1
End If
and make the data type of the return value an integer number rather than a
string.
Ken Sheridan
Stafford, England