Fiscal Calendar Date Function

[dailem]

I'm writing a function that when finished will allow the user to
provide a date & it will return the fiscal month & year. It is in it's
early stages right now- basically just handling days between 6-25, as
they always fall in the calendar month/year. Problem is that when I try
to use the function, it doesn't work correctly. I even try using the
'Day' function to return the day #, but it only works if I surround it
with '#'s' on each side. I'm sure I'm overlooking something simple-
below is my code:

Function CNGFiscal(FiscalDate As Date) As String
'Create function which will return the fiscal year & month
'designed by dm, OCT-2006

Dim myYear As Integer
Dim myMonth As Double
Dim myDay As Integer

Dim FiscalYear As Integer
Dim FiscalMonth As Double

myYear = Year(FiscalDate) 'pull calendar year from input
myMonth = Month(FiscalDate) 'pull calendar month from input
myDay = Day(FiscalDate) 'pull calendar day # from input

Select Case myDay
Case 6 To 24
FiscalMonth = myMonth
FiscalYear = myYear
CNGFiscal = myMonth & "-" & myYear
Case 1 To 5
CNGFiscal = "one to five"
Case 25 To 31
CNGFiscal = "higher than twenty-five"

End Select

CNGFiscal = "not working"
End Function

....any thoughts, I'd appreciate it.

 

[Gary Brown]

The only way I could get this function to NOT work was by actually entering
the following in a cell...
=CNGFiscal(10/15/2006)
This evaluates to 0.000332336324360253 or a date of 12:00:29 am on January
0, 1900.
You need to either point your function to a cell with a date in it, such as
=CNGFiscal(A1) or enter the function with quotes such as
=CNGFiscal("10/15/2006").

For future consideration, when posting, tell us more than 'it doesn't work
correctly'. Not a whole lot to go on.
--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[Tom Ogilvy]

try it this way:

Function CNGFiscal(FiscalDate As Date) As String
'Create function which will return the fiscal year & month
'designed by dm, OCT-2006

Dim myYear As Integer
Dim myMonth As Double
Dim myDay As Integer

Dim FiscalYear As Integer
Dim FiscalMonth As Double

myYear = Year(FiscalDate) 'pull calendar year from input
myMonth = Month(FiscalDate) 'pull calendar month from input
myDay = Day(FiscalDate) 'pull calendar day # from input

CNGFiscal = "not working"

Select Case myDay
Case 6 To 24
FiscalMonth = myMonth
FiscalYear = myYear
CNGFiscal = myMonth & "-" & myYear
Case 1 To 5
CNGFiscal = "one to five"
Case 25 To 31
CNGFiscal = "higher than twenty-five"

End Select


End Function

seemed to work for me.
? CNGFiscal("Oct 12, 2006")
10-2006