Fiscal Year function

[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

 

[Rolls]

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.

 

[Rolls]

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.

 

[Bre-x]

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.

 

[Chris Nebinger]

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

 

[Randy Harris]

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

 

[Bre-x]

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