convert 041670 to April 16, 1970

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
I have a text box on a user form that accepts a date. The userform has to handle the conversion rathern than a cell format.

this is what i am using

TextBox1.Value = Format(TextBox1.Value, "mmmm dd, yyyy")

But if the user puts in 041670 i get January 31, 2014.

what is the best way to handle this? I assume the solution is somewhere in forcing the user to type the date in the text box in a specific way. Since i am striving for consistancy. what is the best way to make sure they do this everytime. Or does the solutio lie elsewhere?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

westjelly

Board Regular
Joined
Jul 5, 2005
Messages
50
I need a nap

Any value in splitting the text box into 3, grabbing a month, day and year? From there, smoosh the 3 values together in whichever format you'd like.

Or would that just be annoying?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Perhaps:

Code:
If IsNumeric(TextBox1) Then TextBox1 = Format(TextBox1.Value, "00-00-00")
TextBox1.Value = Format(TextBox1.Value, "mmmm dd, yyyy")
 

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
thanks that works

west jelly. I thought about putting thre boxes but from a user stand point that seemed clunky. Although it would have ben the easiest. I might have even used 3 list boxes with months days and about a ten year spread.

Hotpepper. that worked fine. My only problem is that if you put in 04161970 it throws it off. if they put in 041670 then it works beautifully. I think i may put some sort of handling on what they enter to make sure they enter correctly. Thank again
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
OK, how about:

Code:
If IsNumeric(TextBox1) Then
    If Len(TextBox1) < 7 Then
        TextBox1 = Format(TextBox1.Value, "00-00-00")
    Else
        TextBox1 = Format(TextBox1.Value, "00-00-0000")
    End If
End If
TextBox1.Value = Format(TextBox1.Value, "mmmm dd, yyyy")
 

Forum statistics

Threads
1,136,262
Messages
5,674,709
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top