Dear All,

I am writing in VBA for Excel 2003 a function to validate Date values which
are inputted via a set of combo boxes, one each for day, month and date. I
am currently puzzling how to go about convering the string "DD\MM\YYYY" into
a true date value to check if it is a real date.

The code is given below - I'm sure I'm missing something simple....

Thanks for any assistance.

Regards

Function Parse_Date(Day As String, Month As String, Year As String, Source
As String) As Boolean
Dim FullDate As String

FullDate = Day & "/" & Month & "/" & Year

Dim DateFormatted As Date

DateFormatted = Format(FullDate, "longdate")

Do While (IsDate(DateFormatted) <> True)
MsgBox (FullDate & " entered in " & Source & " is not a real date.")
Loop

Parse_Date = True

End Function

The below function returns a boolean. Arguments to be passed date string and
format string

If IsValidDate("23-12-1997", "dd-mm-yyyy") = False Then
MsgBox "Invalid Date"
End If

Function IsValidDate(strDate, strFmt)
Dim intx, strDD, strMM, strYY
If Len(strDate) <> Len(strFmt) Then IsValidDate = False: Exit Function
For intx = 1 To Len(strDate)
Select Case (Mid(UCase(strFmt), intx, 1))
Case "D"
strDD = strDD & Mid(UCase(strDate), intx, 1)
Case "M"
strMM = strMM & Mid(UCase(strDate), intx, 1)
Case "Y"
strYY = strYY & Mid(UCase(strDate), intx, 1)
End Select
Next
If CInt("0" & strMM) < 1 Or CInt("0" & strMM) > 12 Then IsValidDate = False:
Exit Function
If strDD = "" Then strDD = "1"
If strYY = "" Then strYY = Year(Date)
IsValidDate = IsDate(strDD & " " & MonthName(CInt(strMM)) & ", " & strYY)
End Function

--
If this post helps click Yes
---------------
Jacob Skaria


"Sardonic" wrote:

> Dear All,
>
> I am writing in VBA for Excel 2003 a function to validate Date values which
> are inputted via a set of combo boxes, one each for day, month and date. I
> am currently puzzling how to go about convering the string "DD\MM\YYYY" into
> a true date value to check if it is a real date.
>
> The code is given below - I'm sure I'm missing something simple....
>
> Thanks for any assistance.
>
> Regards
>
> Function Parse_Date(Day As String, Month As String, Year As String, Source
> As String) As Boolean
> Dim FullDate As String
>
> FullDate = Day & "/" & Month & "/" & Year
>
> Dim DateFormatted As Date
>
> DateFormatted = Format(FullDate, "longdate")
>
> Do While (IsDate(DateFormatted) <> True)
> MsgBox (FullDate & " entered in " & Source & " is not a real date.")
> Loop
>
> Parse_Date = True
>
> End Function

First off, the names of your arguments (Day, Month, Year) are not the best
as these are the names of built-in VB functions. Second, the loop you are
running is not a real loop and can probably be replaced with a simple
If..Then block. Also, what is the Source argument doing? Anyway, it is not
clear to me what your function is supposed to be doing. My guess, though, is
you want know if the inputted day, month and year values produce a valid
date or not. If that is what you are doing, then consider something like
this...

Function Parse_Date(D As String, M As String, Y As String, _
Source As String) As Boolean
Dim DateIn As Date
DateIn = DateSerial(Y, M, D)
Parse_Date = Year(DateIn) = Y And Month(DateIn) = M And Day(DateIn) = D
If Not Parse_Date Then
MsgBox D & "/" & M & "/" & Y & " entered in " & _
Source & " is not a real date."
End If
End Function

--
Rick (MVP - Excel)


"Sardonic" <(E-Mail Removed)> wrote in message
news:F96C3014-1782-4D40-ACB6-(E-Mail Removed)...
> Dear All,
>
> I am writing in VBA for Excel 2003 a function to validate Date values
> which
> are inputted via a set of combo boxes, one each for day, month and date.
> I
> am currently puzzling how to go about convering the string "DD\MM\YYYY"
> into
> a true date value to check if it is a real date.
>
> The code is given below - I'm sure I'm missing something simple....
>
> Thanks for any assistance.
>
> Regards
>
> Function Parse_Date(Day As String, Month As String, Year As String, Source
> As String) As Boolean
> Dim FullDate As String
>
> FullDate = Day & "/" & Month & "/" & Year
>
> Dim DateFormatted As Date
>
> DateFormatted = Format(FullDate, "longdate")
>
> Do While (IsDate(DateFormatted) <> True)
> MsgBox (FullDate & " entered in " & Source & " is not a real date.")
> Loop
>
> Parse_Date = True
>
> End Function

can't you just use a DateTimePicker control?

you could use the ISDATE() function against the string

"Sardonic" <(E-Mail Removed)> wrote in message
news:F96C3014-1782-4D40-ACB6-(E-Mail Removed)...
> Dear All,
>
> I am writing in VBA for Excel 2003 a function to validate Date values
> which
> are inputted via a set of combo boxes, one each for day, month and date.
> I
> am currently puzzling how to go about convering the string "DD\MM\YYYY"
> into
> a true date value to check if it is a real date.
>
> The code is given below - I'm sure I'm missing something simple....
>
> Thanks for any assistance.
>
> Regards
>
> Function Parse_Date(Day As String, Month As String, Year As String, Source
> As String) As Boolean
> Dim FullDate As String
>
> FullDate = Day & "/" & Month & "/" & Year
>
> Dim DateFormatted As Date
>
> DateFormatted = Format(FullDate, "longdate")
>
> Do While (IsDate(DateFormatted) <> True)
> MsgBox (FullDate & " entered in " & Source & " is not a real date.")
> Loop
>
> Parse_Date = True
>
> End Function