Hi,
I have a VBA function in excel that checks a date field to see if it is
null/ in past or in future.

The problem is even if the filed is blank the function returns info stating
it is in the past

Here is the function

Function CheckDate(dtTest as Date)

If Len(Trim(dtTest)) = 0 Then 'Tried IsNull - did not work
CheckDate = "BLANK"
ElseIf dtTest < Date Then
CheckDate = "PAST"
Else
CheckDate = "Future"
End If

Is there a way to check if the date field is "empty'

TIA

This worked for me

Function checkdate(dttest As Variant)

If dttest = "" Then
'returns no value when the dttest is blank
Else
If DateSerial(Year(dttest), Month(dttest), Day(dttest)) _
DateSerial(Year(Now), Month(Now), Day(Now)) Then
checkdate = "Future"
Else
checkdate = "Past"
End If
End If

End Function



shikarishambu wrote:
Hi,
I have a VBA function in excel that checks a date field to see if it is
null/ in past or in future.

The problem is even if the filed is blank the function returns info stating
it is in the past

Here is the function

Function CheckDate(dtTest as Date)

If Len(Trim(dtTest)) = 0 Then 'Tried IsNull - did not work
CheckDate = "BLANK"
ElseIf dtTest < Date Then
CheckDate = "PAST"
Else
CheckDate = "Future"
End If

Is there a way to check if the date field is "empty'

TIA

What is "dtTest"?

If it is a named cell, then try referencing it:

Range("dtTest")

example -
If Len(Trim(Range("dtTest"))) = 0 Then

Regards,
Paul

"shikarishambu" wrote in message
...
Hi,
I have a VBA function in excel that checks a date field to see if it is
null/ in past or in future.

The problem is even if the filed is blank the function returns info
stating it is in the past

Here is the function

Function CheckDate(dtTest as Date)

If Len(Trim(dtTest)) = 0 Then 'Tried IsNull - did not work
CheckDate = "BLANK"
ElseIf dtTest < Date Then
CheckDate = "PAST"
Else
CheckDate = "Future"
End If

Is there a way to check if the date field is "empty'

TIA