This code works when x = a date, but if the cell is blank it displays a "2"
not the error message Case Else should display. Any ideas why?

Function GetFiscalQuarter(ByVal x As Date) 'this works as variant also
Dim Qtr As Date 'this works as integer or variant
Qtr = DatePart("m", x)

Select Case Qtr
Case 7, 8, 9
Qtr = "1"
Case 10, 11, 12
Qtr = "2"
Case 1, 2, 3
Qtr = "3"
Case 4, 5, 6
Qtr = "4"
Case Else
Qtr = "error" 'this doesn't work at all!!!!
End Select

GetFiscalQuarter = Qtr

End Function

--
Robert

datepart("m",0) = 12

Might try wrapping it in an 'if x is numeric' type of clause

It is a lot easier than that. VBA recognizes q as a vaid format.

Function GetFiscalQuarter(ByVal x As Date) as Integer
GetFiscalQuarter = Cint(fomat(x, "q"))
End Function

--
HTH...

Jim Thomlinson


"Robert" wrote:

This code works when x = a date, but if the cell is blank it displays a "2"
not the error message Case Else should display. Any ideas why?

Function GetFiscalQuarter(ByVal x As Date) 'this works as variant also
Dim Qtr As Date 'this works as integer or variant
Qtr = DatePart("m", x)

Select Case Qtr
Case 7, 8, 9
Qtr = "1"
Case 10, 11, 12
Qtr = "2"
Case 1, 2, 3
Qtr = "3"
Case 4, 5, 6
Qtr = "4"
Case Else
Qtr = "error" 'this doesn't work at all!!!!
End Select

GetFiscalQuarter = Qtr

End Function

--
Robert

Jim, thanks but I tried this and it still doesn't seem to work.
--
Robert


"Jim Thomlinson" wrote:

It is a lot easier than that. VBA recognizes q as a vaid format.

Function GetFiscalQuarter(ByVal x As Date) as Integer
GetFiscalQuarter = Cint(fomat(x, "q"))
End Function

--
HTH...

Jim Thomlinson


"Robert" wrote:

This code works when x = a date, but if the cell is blank it displays a "2"
not the error message Case Else should display. Any ideas why?

Function GetFiscalQuarter(ByVal x As Date) 'this works as variant also
Dim Qtr As Date 'this works as integer or variant
Qtr = DatePart("m", x)

Select Case Qtr
Case 7, 8, 9
Qtr = "1"
Case 10, 11, 12
Qtr = "2"
Case 1, 2, 3
Qtr = "3"
Case 4, 5, 6
Qtr = "4"
Case Else
Qtr = "error" 'this doesn't work at all!!!!
End Select

GetFiscalQuarter = Qtr

End Function

--
Robert

It would work better if I knew how to type... :-)

Format should be Format...

Function GetFiscalQuarter(ByVal x As Date) As Integer
GetFiscalQuarter = CInt(Format(x, "q")) 'Typo...
End Function

--
HTH...

Jim Thomlinson


"Robert" wrote:

Jim, thanks but I tried this and it still doesn't seem to work.
--
Robert


"Jim Thomlinson" wrote:

It is a lot easier than that. VBA recognizes q as a vaid format.

Function GetFiscalQuarter(ByVal x As Date) as Integer
GetFiscalQuarter = Cint(fomat(x, "q"))
End Function

--
HTH...

Jim Thomlinson


"Robert" wrote:

This code works when x = a date, but if the cell is blank it displays a "2"
not the error message Case Else should display. Any ideas why?

Function GetFiscalQuarter(ByVal x As Date) 'this works as variant also
Dim Qtr As Date 'this works as integer or variant
Qtr = DatePart("m", x)

Select Case Qtr
Case 7, 8, 9
Qtr = "1"
Case 10, 11, 12
Qtr = "2"
Case 1, 2, 3
Qtr = "3"
Case 4, 5, 6
Qtr = "4"
Case Else
Qtr = "error" 'this doesn't work at all!!!!
End Select

GetFiscalQuarter = Qtr

End Function

--
Robert