Date to Date

[pekajo]

Hi,
Hope you can help.
I have a field 'short text' with 'Thursday 10th October'
Is there an easy way of converting to 10/10/2019 in date field with VBA

Thanks for any help
Peter

 

[June7]

Easy is perception. Sure, VBA can do.

Why use 2019?

Something like:

Function TextToDate(strDate As String) As Date
Dim aryD As Variant
aryD = Split(strDate, " ")
TextToDate = CDate(aryD(2) & "/" & Val(aryD(1)) & "/" & Year(Date))
End Function

 

[arnelgp]

format(fieldName, "short date")

or

format(fieldName, "mm/dd/yyyy")

or

CDate(fieldname)

 

[isladogs]

Any of the solutions arnel offered will work if your field just contains a date string such as 10 October 2019 or even 10 October.
However including Thursday and 'th' in your text field will prevent those working. In which case you need a UDF such as that suggested by June.

 

[smig]

Any reason to save a date field as text?

 

[apr pillai]

Public Function Date2txt(ByVal dt As Date) As String
Dim txt As String, num As Integer
num = Day(dt)

   Select Case num
       Case 1, 21, 31
          txt = "st "
       Case 2, 22
          txt = "nd "
       Case 3, 23
          txt = "rd "
       Case 4 To 20, 24 To 30
          txt = "th "
   End Select
   
   Date2txt = WeekdayName(Weekday(dt)) & "," & Day(dt) & txt & MonthName(Month(dt)) & " " & Year(dt)
   
End Function

 

[isladogs]

Hi Apr
That's a nice function but the OP wants to do the opposite i.e. convert text to a date

 

[apr pillai]

Yes, you are right. I overlooked some part on the question. June7 has already posted the correct Function.

 

[isladogs]

That looks suitable but of course is very similar to June's version in post #2.
Yours uses "-" separators, June's has "/"

Personally I would use CDate in the last line to ensure the local date format is displayed

Public Function Text2Date(ByVal txtDate As String) As String
Dim S, dt As String

    S = Split(txtDate, " ")
    dt = Str(Val(S(1))) & "-" & S(2) & "-" & Trim(Str(Year(Date))) 'or use / instead of -
    Text2Date = [B]CDate(DateValue(dt))[/B]

End Function

EDIT:
I answered before you removed your function