Fiscal Year function

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

Bre-x

Hi,
I have a table with GL entries, i need to find out what fiscal year each
entry belogns to what FY (fiscal year).

Example 12/17/2001 will be FY 2001 because 2001 FY goes from 07/01/2001 to
07/31/2002, and soo on

i have a function but it's not working:


Function FY(D As Date)
Select Case D
Case D <= "8/31/2002"
FY = 2001
Case D >= "8/31/2002" And D <= "8/31/2003"
FY = 2003
Case Else
FY = 0
End Select
End Function

Any idea what's wrong with it?

John
 
Your first CASE statement should return FY=2002, not 2001. Also your second
CASE statement should begin with GREATER THAN not greater than or equal to
8/31.
 
Another thing to consider would be to make a table with three columns:

FiscalYear
Fiscal Quarter
TransactionDate

If you has a 7/31 FYE the first three fields would be:

2004
Q1
08/01/2003

....

for a total of 365 records. Then do a join to your G/L data. This will let
you sum by quarter for all fiscal years & fiscal quarters in the data.
 
Never mind the select case dates
I know the function does not work because i am using the wrong format for
the date, because i get 0 for all records.
I think it has to do with "8/31/2002" or is it like 8 / 31 /2002
anyways the function does not work.
 
I wouldn't hard code anything into the Select Case, as it
won't work for any date range outside of what you
preprogram. I think that your FY starts 5 months before
the CY, correct? If so, try:

Public Function FiscalYear(dteFY)
If IsDate(dteFY) Then
FiscalYear = Year(DateAdd("m",-5,dteFY))
End If

End Function


Chris Nebinger
 
This is one of those challenges that must have more than a dozen ways of
doing it. This is what I came up with. (tested)

Year(D) + (DatePart("y", D) < DatePart("y", "9/1/" & Year(D)))

where dDate is, of course, the Date

My guess is that it can probably be done, perhaps more efficiently, with
DateSerial.

Regards,
Randy
 
Thank everyone for you help

Randy your funcion works very well just a small change

Year(D) + (DatePart("y", D) < DatePart("y", "9/1/" & Year(D)))

it has to be 08/01 A Fiscal year start on August
 
Back
Top