PDA

View Full Version : Solved: Format Textbox to 19XX



John_Mc
05-02-2006, 11:00 PM
Hi All,

I've currently got the following:


Sub txtDateofBirth1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtDateOfBirth1.Text = Format(txtDateOfBirth1.Value, "DD/MM/YYYY")
End Sub


placed on a exit event on a userform. However, where a user enters 3/3/27, for example, it defaults to 2027. I actually need it to say 1927.

I've tried subtracting dates but am stuck...:banghead: can someone point me in the right direction?

Cheers,
John

geekgirlau
05-02-2006, 11:33 PM
This is due to the way Windows is setup to interpret 2-digit dates. Go to Control Panel | Regional Settings | Date (may need to select Customise first depending on which version of Windows you are using. By default the range is 1930 to 2029, which means 27 is interpretted as 2027. Adjust it down as far as you need to for your application.

John_Mc
05-02-2006, 11:59 PM
Hi GeekGirlAu,

thanks for getting back to me - the problem is that multiple users will be using this spreadsheet, so I'd prefer not to have to change all their settings (and for anyone else who joins etc).

Is there another way? I suppose I could remove the formatting code and write an if statement instead. Say "if the cell only has 2 digits (yy), put 19 in front of it, otherwise if it is 4 digits (yyyy) then leave it alone"

Would this be the best approach, or can you think of another?:dunno

Cheers,
John Mc

jindon
05-03-2006, 12:54 AM
Hi
try

Private Sub txtDateOfBirth1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim mItem As Object, minYear As Long, myYear
minYear = (Year(Date) + 1) Mod 100
With CreateObject("VBScript.RegExp")
.Pattern = "\d{2}$"
If .test(Me.txtDateOfBirth1) Then
Set mItem = .Execute(Me.txtDateOfBirth1.Text)
If Val(mItem.Item(0)) >= minYear Then
myYear = "19"
Else
myYear = "20"
End If
Me.txtDateOfBirth1.Text = _
.Replace(Me.txtDateOfBirth1.Text, myYear & mItem.Item(0))
End If
End With
Me.txtDateOfBirth1.Text = Format(Me.txtDateOfBirth1.Text, "DD-MM-YYYY")
End Sub

Tommy
05-03-2006, 09:25 AM
Here is a one liner but it works for 1910 to 2009
Sub txtDateofBirth1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtDateofBirth1.Text = IIf(Val(Right(txtDateofBirth1.Text, 2)) < 10, Format(txtDateofBirth1.Text, _
"DD/MM/YYYY"), Replace(Format(txtDateofBirth1.Text, "DD/MM/YYYY"), "/20", "/19"))
End Sub


EDIT: - fixed formatting

John_Mc
05-07-2006, 09:50 PM
Hi All,

Thank you all for your replys. I'll use the one-line version now as it will work perfectly for this template and I can call this project finished.

I will learn then from Jindons code and integrate that into another project i'll start working on in the next week or so, which will need something similiar.

Thanks for all your time, I'll mark this one as solved! :thumb

John Mc