Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a String into a date format
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a String into a date format
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a String into a date format
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a String into a date format
can't you just use a DateTimePicker control?
you could use the ISDATE() function against the string "Sardonic" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a string into a number format | Excel Discussion (Misc queries) | |||
Converting a time format to a String format in Excel | Excel Discussion (Misc queries) | |||
Converting a Date to a string | Excel Programming | |||
Converting a Date to a string | Excel Programming | |||
Converting a string date into a Excel Date | Excel Programming |