Entering wrong date ie 30-2-19 (30-Feb-2019) in txtDate.text it displays as 19-Feb-1930 instead of showing error msg

[SamDsouza]

Hello

Have an issue. when entering wrong date ie 30-2-19 (30-Feb-2019) in txtDate.text it displays as 19-Feb-1930. i dont know why.
Infact the below code has the msg of Invalid Date. but it by passes and shows wrong date altogether. The coding should not display any wrong dates

Option Explicit
Dim disableEvents As Boolean
Public dDate As Date

Private Sub txtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   Worksheets("Sheet1").Columns(1).NumberFormat = "dd-mmm-yyyy"

    If txtDate.Value = vbNullString Then
       Exit Sub
    ElseIf Not IsDate(txtDate.Value) Then
       Cancel = True  
        MsgBox "Invalid date, please re-enter", vbCritical
        txtDate.Value = vbNullString
        txtDate.SetFocus
        Worksheets("Sheet1").Range("A2").Value = Format(CDate(txtDate.Value), "dd-mmm-yyyy")
     Exit Sub
End If
     dDate = DateSerial(Year(Date), Month(Date), Day(Date))
     txtDate.Value = Format(CDate(txtDate.Value), "dd-mmm-yyyy")
     Worksheets("Sheet1").Range("A2").Value = Format(CDate(txtDate.Value), "dd-mmm-yyyy")
End Sub

Private Sub UserForm_Initialize()
  Load userform1
  userform1.Show vbModeless
  With txtDate
     .Value = Worksheets("Sheet1").Range("A2").Value
     If .Value <> "" Then .Value = Format(.Value, "dd-mmm-yyyy")
  End With
 End Sub

SamD

 

[DanteAmor]

You could add another validation to verify if the capture has 10 characters. For example:

ElseIf Not IsDate(txtDate.Value) Or Len(txtDate.Value) <> 10 Then

 

[SamDsouza]

Thanks DanteAmor for your suggestion for adding another validation. Whats happening is i ve to compulsory add 10 digits
What is desired is Entering dates with the flexibility
as
1. 25-01-01 result as 25-Jan-2001
2. 2-3-14 result as 02-Mar-2014
3.02-03-2014 result as 02-Mar-2014
4. 02-3-14 result as 02-Mar-2014
5. 2-03-14 result as 02-Mar-2014

only if wrong date entered by mistake then Error Msg

 

[NimishK]

Try the following. This is where you might have better control and validation

Private Sub txtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If txtDate.Value = vbNullString Then
   txtDate.Value = Format(CDate(txtDate.Value), "dd-mmm-yyyy")
    Exit Sub
ElseIf Not IsDate(txtDate.Value) Or Mid(txtDate.Value, 4, 2) > 12 Then
        Cancel = True
        MsgBox "Invalid date, please re-enter", vbCritical
        Cancel = False
        txtDate.Value = vbNullString
        txtDate.SetFocus
    Exit Sub
ElseIf Not IsDate(txtDate.Value) Or Mid(txtDate.Value, 3, 1) <> "-" Then
        MsgBox "You missed the Hyphen before the Month, Kindly Renter Date", vbCritical
        'Application.EnableEvents = false
        txtDate.Value = vbNullString
        txtDate.SetFocus
        'Application.EnableEvents = True
        Exit Sub


ElseIf Not IsDate(txtDate.Value) Or Mid(txtDate.Value, 6, 1) <> "-" Then
        MsgBox "You missed the Hyphen before the Year, Kindly Renter Date", vbCritical
        'Application.EnableEvents = false
        txtDate.Value = vbNullString
        txtDate.SetFocus
        'Application.EnableEvents = True
        Exit Sub
End If
dDate = DateSerial(Year(Date), Month(Date), Day(Date))
txtDate.Value = Format(CDate(txtDate.Value), "dd-mmm-yyyy")

NimishK

 

[steve the fish]

How is excel to know what year you mean when you just enter 2 digit year? What if the year was say 99? Is that 1999 or 2099? Your initial problem was excel trying to be helpful knowing there isnt 30 days in Feb. A 4 digit year solves that so as long as we know your rules for 2 digit years they can be altered.

 

[SamDsouza]

Thanks NimishK. Tried yours but then i've to put Two digit Nos for Day and Month and Four Digit for year. I think 4 digit for year is good idea rather than 2 digit year.
Also the same suggestion from Steve the Fish for 4 digit year.
Can you guys come for solution for 1 digit day and 1 digit month and 2 digit year. This is to speed up when entring the date.

 

[steve the fish]

If you enter 2 digit year which year is it? Is 99 the year 1999 or 2099? Is 30 the year 1930 or 2030? This is what is causing your problem but you need to answer the question. I cant answer that for you.

 

[welshgasman]

Split the string and use the components to build a valid date.
For instance if year is >=50 then century = 19 else it's 20

That should last you a while?

HTH

 

[SamDsouza]

Steve the Fish

If you enter 2 digit year which year is it? Is 99 the year 1999 or 2099? Is 30 the year 1930 or 2030? This is what is causing your problem but you need to answer the question. I cant answer that for you.

Yes, Agreed definately you cant answer for me. Your point is worth considering 2 digit or 4 diigt.
As users we tend to type 2 digit for Speed up. Well considering now will be 2000 onwards if 2 digit

Welshgasman
Thanks for your reply. Looking for common and lasting solution.

SamD

 

[welshgasman]

What I was trying to point out that eventually the 2 digit year test is going to fail. By that time you would likely be using new software. :D
Only you know your data, so if there is a chance of someone entering 01-01-99 then which century is that.? I would hazard a guess as to 1999. If you are putting dates in as 2099 how long do you think your software is going to run?

If it were me, I would create a dedicated function that would work out the correct date, allow for -,. & / delimiters etc and call in the AfterUpdate event of the controls.
Once written and tested, that should be it.

Steve the Fish

Yes, Agreed definately you cant answer for me. Your point is worth considering 2 digit or 4 diigt.
As users we tend to type 2 digit for Speed up. Well considering now will be 2000 onwards if 2 digit

Welshgasman
Thanks for your reply. Looking for common and lasting solution.

SamD