converting date from a textbox to a date format

[neowok]

not as easy as it may sound because I currently have 2 columns in dat
format of dd-mmm-yy i.e. 05-Jan-04 and using cdate to convert from th
text box only seems to put it into dd/mm/yyyy format. I would reall
like to keep the dd-mmm-yy format that is used in the columns as i
makes it easier to see than 05/02/2004 when you have a big list o
dates.

So I need a way of converting the text the user types into the text bo
into date format dd-mmm-yy so that i can use autofilter to search it

 

[Bernie Deitrick]

neo,

Try something like

myDateStr = Format(CDate(Textbox.Text), "dd-mmm-yyyy")

HTH,
Bernie
MS Excel MVP

 

[neowok]

ok ill give it a try monday

thank

 

[neowok]

just tried that but isnt working, code i used was

Private Sub CommandButton4_Click()
Dim mydatestr As String
mydatestr = Format(CDate(TextBox2.value), "dd-mmm-yyyy")
Range("J23").Value = mydatestr
Me.Range("mytables").AutoFilter 6, mydatestr, _
Operator:=xlAnd
End Sub

only way ive got it to return a result so far is by

Me.Range("mytables").AutoFilter 6, cdate(textbox2.text), _
Operator:=xlAnd but i had to convert the column to the first dat
format *dd/mm/yyyy which seems to be what cdate converts to

 

[Bernie Deitrick]

Neowok,

Your code worked fine for me if:

1) the entire column J was formated custom dd-mmm-yyyy
2) I removed the Me. before the autofilter method.

HTH,
Bernie
MS Excel MVP

 

[neowok]

ahh thanks, ill try removing the ME then (dont know exactly what that
for anyway