Having problems With Year Representation (VBA)

[Ark68]

I have a userform for which the user enters a date as "mmddyy" into a textbox (tbx_date). A function then validates the entry as being a valid date.
With a valid user entry (ie date), I run the following code intended to replace the value of tbx_date with a value formatted as "dd-mmm-yyyy".

...
        idate = tbx_date.Value
        mm = CInt(Left(idate, 2))
        mtxt = MonthName(mm, True)
        dd = CInt(Mid(idate, 3, 2))
        yy = CInt(Right(idate, 2))

mbevents = False 'suspends triggers for value updating
tbx_date.Value = dd & "-" & mtxt & "-" & Format(yy, "yyyy")
mbevents = True
...[/code]

A user entering a valid entry of 071004 (July 10 2004) results with an improper value of tbx_date as 10-Jul-1900.

What must I do to correct this so that the year is represented as 2004?

 

[Kaper]

Shouldn't be

yy = CInt("20" & Right(idate, 2))

 

[Ark68]

Shouldn't be

yy = CInt("20" & Right(idate, 2))

Thanks Kaper. I see the logic, and now I realized that my approach won't work. Since some years are from the 1900s I will need the user to enter the full year.

 

[Fuji]

I have a userform for which the user enters a date as "mmddyy" into a textbox (tbx_date). A function then validates the entry as being a valid date.

A user entering a valid entry of 071004 (July 10 2004) results with an improper value of tbx_date as 10-Jul-1900.
What must I do to correct this so that the year is represented as 2004?

4 digits year is the best, but if you prefer 2 digits then
One way

Private Sub tbx_date_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim d
    d = GetDate(Me.tbx_date)
    If IsDate(d) Then
        Me.tbx_date = Format$(d, "mmm dd yyyy")
    Else
        MsgBox "Invalid entry", , Me.tbx_date
    End If
End Sub

Function GetDate(s$)
    Dim d
    If s Like "######" Then
        d = DateSerial(Right(s, 2), Left(s, 2), Mid(s, 3, 2))
        If Format(d, "mmddyy") = s Then GetDate = d
    End If
End Function

Century of 2 digits year will be determined by the regional settings.

Excel works with two-digit year numbers - Microsoft 365 Apps

Describes how Microsoft Excel determines the century when you type a date using a two-digit year number.

learn.microsoft.com

 

[Alex Blakenburg]

@Fuji, that's a risky approach. It depends on the computer's regional setting which may be different from one computer to the next eg the default to use the 21st century is <= 29 but I have mine set to 49. I would have thought it would be better to take control over the cut-over point in the code.
To check your setting:

• <win>+R
• type in control and hit enter (to bring up the Control Panel)
• Select Region > Additional Settings > Formats > Date

 

[RoryA]

You also didn't want this part: Format(yy, "yyyy") That was what was causing you to get 1900.

 

[Fuji]

@Fuji, that's a risky approach. It depends on the computer's regional setting which may be different from one computer to the next eg the default to use the 21st century is <= 29 but I have mine set to 49. I would have thought it would be better to take control over the cut-over point in the code.
To check your setting:

As I mentioned

4 digits year is the best, but if you prefer 2 digits then
One way

 

[Ark68]

Thank you all for your support. I've opted to simply go with entering a 4 digit year. Alex and Fuji, your contributions were enlightening and gave me some greater understanding (although working with and formatting dates and times in Excel I still find confusing), of the concept.

Thank you Rory. I would have been avoided that with the implemtation of Kaper's suggestion. I did that not knowing how to take a two digit integer representing the trailing end of a 4 digit year into 4 digits that I could use in a strring.

 

[Alex Blakenburg]

You're welcome. Glad we could help.