"rpboll" <RP****@gmail.comwrote in
news:11**********************@k70g2000cwa.googlegr oups.com:
Is there a function that stamps the FISCAL Date (Starting from
October)?
I am trying to generate a Fiscal Date and an AutoNumber for a key
field.
What kind of number would you want? Do you want October dates to be
recorded as January? Or a day number from September 30th?
Date() returns the current date. You should probably write a
user-defined function to handle the calculation.
To return the Year part of the fiscal year, you'd do this:
Function FiscalYear(dteDate As Date) As Integer
Dim intMonth As Integer
intMonth = Month(dteDate)
If intMonth < 10 Then
FiscalYear = Year(dteDate) - 1
Else
FiscalYear = Year(dteDate)
End If
End Function
If you want to return the beginning of the fiscal year for a
particular date, you'd alter that to this:
Function FiscalYearStart(dteDate As Date) As Date
Dim intMonth As Integer
Dim intYear As Integer
intMonth = Month(dteDate)
If intMonth < 10 Then
intYear = Year(dteDate) - 1
Else
intYear = Year(dteDate)
End If
FiscalYearStart = DateSerial(intYear, 10, 1)
End Function
Another approach to getting the year would be:
Year(DateAdd("m", -9, Date()))
and then you could use:
Format("10/1" & Year(DateAdd("m", -9, Date())), "m/d/yyyy")
and that would give it to you.
Now, what you want in your fiscal date field, I don't know. If you
want the number of days since the beginning of the fiscal year,
you'd use the function above like this:
Date() - FiscalYearStart(Date())
That will give you the number of days since the begining of the
fiscal year. If you want the ordinal number, you'd add 1, since the
expression above gives zero for the first day of the fiscal year (as
to be expected).
--
David W. Fenton
http://www.dfenton.com/
usenet at dfenton dot com
http://www.dfenton.com/DFA/ 
