Date to Date (1 Viewer)

pekajo

Registered User.
Local time
Tomorrow, 06:37
Joined
Jul 25, 2011
Messages
132
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

AWF VIP
Local time
Today, 11:37
Joined
Mar 9, 2014
Messages
5,423
Easy is perception. Sure, VBA can do.

Why use 2019?

Something like:
Code:
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
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:37
Joined
May 7, 2009
Messages
19,169
format(fieldName, "short date")

or

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

or

CDate(fieldname)
 

isladogs

MVP / VIP
Local time
Today, 19:37
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 21:37
Joined
Nov 25, 2009
Messages
2,209
Any reason to save a date field as text?
 

apr pillai

AWF VIP
Local time
Tomorrow, 01:07
Joined
Jan 20, 2005
Messages
735
Code:
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

MVP / VIP
Local time
Today, 19:37
Joined
Jan 14, 2017
Messages
18,186
Hi Apr
That's a nice function but the OP wants to do the opposite i.e. convert text to a date
 

apr pillai

AWF VIP
Local time
Tomorrow, 01:07
Joined
Jan 20, 2005
Messages
735
Yes, you are right. I overlooked some part on the question. June7 has already posted the correct Function.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 19:37
Joined
Jan 14, 2017
Messages
18,186
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

Code:
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
 

Users who are viewing this thread

Top Bottom