Can a function returning a date return an empty value?

[RB Smissaert]

Have the following function to change an integer number in the form of
yyyymmdd to a date.
The return value has to be declared as a date.

Function ConvertDates(ByVal intDate As Long) As Date

'to return a date seems to be the only way to
'guarantee that the day and month are not reversed
'the problem is that null values will be converted
'to 12:00:00 AM, this can be dealt with by either
'changing to "" after or
'do ActiveWindow.DisplayZeros = False
'------------------------------------------------------------

If intDate = 0 Or intDate = Null Then
Exit Function
Else
ConvertDates = Right(intDate, 2) & "/" & _
Mid(intDate, 5, 2) & "/" & _
Left(intDate, 4)
End If

End Function

The problem arises when the variable intDate is a NULL value or EMPTY.
In that case the value that eventually appears in the sheet will be 12:00:00
AM
Now is there any way to avoid this other than changing the value afterwards
to ""
or doing ActiveWindow.DisplayZeros = False?
Both these methods have their drawbacks and I would like to handle this in
the function.

Thanks for any advice.


RBS

 

[Dick Kusleika]

RB

You could do it like this

Function ConvertDates(ByVal intDate As Long) As Variant

If intDate = 0 Or intDate = Null Then
ConvertDates = ""
Exit Function
Else
ConvertDates = CDate(Right(intDate, 2) & "/" & _
Mid(intDate, 5, 2) & "/" & _
Left(intDate, 4))
End If

End Function

This converts the date explicitly rather than relying on Excel's built-in
date conversion. However, I would not do it this way. I would not expect a
function to return a blank if there is no date. I would rather see the text
in your formula rather than in the UDF. Use your UDF and a formula like

=IF(A1=0,"",ConvertDates(A1))

 

[RB Smissaert]

Thanks. I had chosen your second option.

RBS

RB

You could do it like this

Function ConvertDates(ByVal intDate As Long) As Variant

If intDate = 0 Or intDate = Null Then
ConvertDates = ""
Exit Function
Else
ConvertDates = CDate(Right(intDate, 2) & "/" & _
Mid(intDate, 5, 2) & "/" & _
Left(intDate, 4))
End If

End Function

This converts the date explicitly rather than relying on Excel's built-in
date conversion. However, I would not do it this way. I would not expect a
function to return a blank if there is no date. I would rather see the text
in your formula rather than in the UDF. Use your UDF and a formula like

=IF(A1=0,"",ConvertDates(A1))


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.