kvnagaraj2000
asked on
How to Normalise the date? (Converting user-defined dates into meaningful date)
How do I convert (normalise) the user-defined dates into meaningful dates?
For example: If the user enters date '29-Feb-2002' must return '1-Mar-2002' (29th Feb is not a valid date and hence the next day is returned).
Similarly, if the user enters '31-30-2000' must return '1-Jul-2003' (As the months 30 is invalid, it must return 30 months from the year 2000 which results June, 2003 but the date is 31st and there is no 31st in June, it must return the next day i.e. 1-Jul-2003).
Also, I must be able to set my own default date for date calculation. For example: If I set Default Date as '01-01-2004' and I pass a date i.e. '10-10-2004', it must return the No. of days from 1st Jan, 2004 which is 284 days and if the user enters 284 days, it must return '10-10-2004'.
The User can enter either '01/01/2004' or '01-Jan-2004' or '01-January-2004' formats. How do I validate the date string?
Thank you Experts in advance.
For example: If the user enters date '29-Feb-2002' must return '1-Mar-2002' (29th Feb is not a valid date and hence the next day is returned).
Similarly, if the user enters '31-30-2000' must return '1-Jul-2003' (As the months 30 is invalid, it must return 30 months from the year 2000 which results June, 2003 but the date is 31st and there is no 31st in June, it must return the next day i.e. 1-Jul-2003).
Also, I must be able to set my own default date for date calculation. For example: If I set Default Date as '01-01-2004' and I pass a date i.e. '10-10-2004', it must return the No. of days from 1st Jan, 2004 which is 284 days and if the user enters 284 days, it must return '10-10-2004'.
The User can enter either '01/01/2004' or '01-Jan-2004' or '01-January-2004' formats. How do I validate the date string?
Thank you Experts in advance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To validate the dates use IsDate - but beware of it as it does this: First it attempts to validate the date in the user's locale, if it fails it will swap the day and month and try again. I have a better conversion routine which I use in my calendar control. I will try and dig it out.
the problem with isdate is that it will completely fail on things like ('31-30-2000').
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
gary_j:
Yes - and IsDate is crap. e.g. A user's locale says dates are in dd/mm/yyyy format and they type in 12/31/2004, Is date says it's ok.
Yes - and IsDate is crap. e.g. A user's locale says dates are in dd/mm/yyyy format and they type in 12/31/2004, Is date says it's ok.
My earlier example distilled for this problem:
Private Const McstrValidSeparators As String = "/. ,~_-"
Private Function Text2Date(ByVal PstrText As String) As Date
Dim LlngIndex As Long
Dim LstrDatepart() As String
On Error GoTo ErrorHandler
PstrText = Trim$(PstrText)
If PstrText = vbNullString then
Err.Raise 5
End If
For LlngIndex = 1 To Len(PstrText)
If Mid$(PstrText, LlngIndex, 1) Like "[" & McstrValidSeparators & "]" Then
Mid$(PstrText, LlngIndex, 1) = "/"
End If
Next
LstrDatepart = Split(PstrText, MstrDateSeparator)
If UBound(LstrDatepart) <> 2 Then
Err.Raise 5
End If
If Not IsNumeric(LstrDatepart(1))
LstrDatepart(1) = DatePart("m", "13 " & LstrDatepart(1) & " 2000")
End If
'in this case entry is d/m/y - LstrDatePart (0) is Day; (1) is Month; (2) is Year
Text2Date = DateSerial(CInt(LstrDatepa
Exit Function
ErrorHandler:
With Err
.Raise .Number, .Source, .Description, .HelpFile, .HelpContext
End With
End Function
Private Const McstrValidSeparators As String = "/. ,~_-"
Private Function Text2Date(ByVal PstrText As String) As Date
Dim LlngIndex As Long
Dim LstrDatepart() As String
On Error GoTo ErrorHandler
PstrText = Trim$(PstrText)
If PstrText = vbNullString then
Err.Raise 5
End If
For LlngIndex = 1 To Len(PstrText)
If Mid$(PstrText, LlngIndex, 1) Like "[" & McstrValidSeparators & "]" Then
Mid$(PstrText, LlngIndex, 1) = "/"
End If
Next
LstrDatepart = Split(PstrText, MstrDateSeparator)
If UBound(LstrDatepart) <> 2 Then
Err.Raise 5
End If
If Not IsNumeric(LstrDatepart(1))
LstrDatepart(1) = DatePart("m", "13 " & LstrDatepart(1) & " 2000")
End If
'in this case entry is d/m/y - LstrDatePart (0) is Day; (1) is Month; (2) is Year
Text2Date = DateSerial(CInt(LstrDatepa
Exit Function
ErrorHandler:
With Err
.Raise .Number, .Source, .Description, .HelpFile, .HelpContext
End With
End Function
points split Gary_j/fds_fatboy?