The following link:
http://www.tek-tips.com/faqs.cfm?fid=1960
includes code for Crystal Reports for returning the accounting month based on
'4-4-5' accounting periods. Its basis is:
1. The accounting year always starts on the first day of the month of the
first month in the fiscal year
2. The Accounting Year always ends on the last day of the month in the last
month of the fiscal year
Each quarter in the year is comprised of Accounting Months as follows:
3. Four weeks in the first month of the quarter, unless the first day of the
fiscal year falls on the last day of the fiscal week - in this case the first
fiscal month is comprised of four weeks plus 1 day
4. Four weeks in the second month of the quarter
5. Five weeks in the third month of the quarter, except for the last fiscal
month since the last accounting month runs all the way to the end of the
fiscal year, regardless of the number of weeks
With a bit of cutting and pasting the code transalates into a VBA function as
follows:
Public Function OpMonth(varDate)
...
A join table like John suggested seems to be the easiest way to
implement the business logic here, but perhaps some have queries that
would make adding another join unreasonably complicated. For them,
maybe:
'Begin Module Code
Public Function GetOperatingMonth455(intFiscalYearStartMonthNum As
Integer, vbLastDayOfFiscalWeek As Integer, dtTestDate As Date) As Date
Dim dtFYStart As Date
Dim dtFYEnd As Date
Dim dtFirstLastDayOfFiscalWeek As Date
Dim dtEndOfFirstFiscalWeek As Date
Dim dtFMEnd(12) As Date
Dim I As Integer
dtFYStart = DateSerial(Year(dtTestDate) - Abs(Month(dtTestDate) <
intFiscalYearStartMonthNum), intFiscalYearStartMonthNum, 1)
dtFYEnd = DateAdd("d", -1, DateAdd("yyyy", 1, dtFYStart))
dtFMEnd(12) = dtFYEnd
dtFirstLastDayOfFiscalWeek = NthXDate(1, vbLastDayOfFiscalWeek,
dtFYStart)
dtEndOfFirstFiscalWeek = DateAdd("d", Abs(dtFYStart =
dtFirstLastDayOfFiscalWeek), dtFirstLastDayOfFiscalWeek)
dtFMEnd(1) = DateAdd("ww", 3, dtEndOfFirstFiscalWeek)
For I = 2 To 11
dtFMEnd(I) = DateAdd("ww", 4 + Abs(I / 3 = I \ 3), dtFMEnd(I - 1))
Next I
For I = 1 To 12
If DateDiff("d", dtTestDate, dtFMEnd(I)) >= 0 Then
GetOperatingMonth455 = DateSerial(Year(dtFYStart), I, 1)
Exit Function
End If
Next I
End Function
Public Function NthXDate(n As Integer, d As Integer, dtD As Date) As
Date
NthXDate = DateSerial(Year(dtD), Month(dtD), (7 - WeekDay(DateSerial
(Year(dtD), Month(dtD), 1)) + d) Mod 7 + 1 + (n - 1) * 7)
End Function
'End Module Code
Sample calls:
GetOperatingMonth455(1, vbSaturday, #1/26/2002#) => 1/1/2002
GetOperatingMonth455(1, vbSaturday, #1/27/2002#) => 2/1/2002
Oops, the varTestDate you used is obviously better than inputting a
Date in case a field used for the test date contains a Null value (an
exercise for the reader). Here 2/1/2002 actually means that the
operating month is the second one of FY2002, so anyone can format the
output as desired by relying on the Variant output type to handle
returning a string or a Null value. The GetOperatingMonth455()
function above seems simpler than having all that Case logic shown in
the link and should handle any starting fiscal month and fiscal end of
week day (someone please let me know if it doesn't).
Note: I only tested the function for a few dates. It seems to behave
in accordance with what I believe the general rules are implying. It
was a fun little exercise.
James A. Fortune
(e-mail address removed)
Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular
procedure, but I am under no obligation to modify these examples to
provide added functionality or to construct procedures to meet your
specific requirements. Any code samples posted contain no known
hidden material defects. However, anyone who uses any code sample
posted does so with the understanding that they are responsible for
any testing of any illustrative code sample for any particular use.
Furthermore, anyone using an illustrative code sample I provide or
code derived from it does so at their own risk.
. The "plus one day" was