Date Formatting Function

[AndrewKent]

Hi there,

I am extracting data from a website and I have a text string that represents a date:

"Friday, August 6, 2010"

It will always be returned in this format and I want to write a formula that will convert this to:

"06/08/2010"

which I can then use.

Any suggestions?

Andy

 

[Desu Nota from Columbus]

I was able to do a text->columns, concatenate the Month/day/year, convert to date value, and then format it.

Is this too roundabout? I don't know how to do it directly in one step.

 

[Norie]

Andy

There are lots of way you could do that.

For example:

If you remove the day and first comma VBA will recognise that as a date.

This code is just to demonstrate that.

Dim strDate As String
 
strDate = "Friday, August 6, 2010"
 
strDate = Mid(strDate, InStr(strDate, ",") + 1)

Debug.Print IsDate(strDate)
 
strDate = Format(strDate, "dd/mm/yyyy")
 
Debug.Print strDate

 

[Sthrncali]

My solution certainly isn't the shortest route, but it worked for me. The only thing you need to change is the RANGE reference that tells the macro what your source range is..

Sub ChangeDate()
Dim OrigTxtDate As String
Dim FormattedDate As String
Dim Month As String
Dim dte As Date
Dim Day As String
Dim Year As String
Dim myRange As Range
Dim NewDate As Date
 
On Error Resume Next
 
Dim s1 As Integer, s2 As Integer, s3 As Integer, s4 As Integer
 
Set myRange = Sheet1.Range("A1:A5") 'Set your range here.
 
For Each cell In myRange
OrigTxtDate = cell.Text
 
s1 = Application.WorksheetFunction.Search(" ", OrigTxtDate, 1) 'Locate first space
s2 = Application.WorksheetFunction.Search(" ", OrigTxtDate, s1 + 1) 'Locate second space
s3 = Application.WorksheetFunction.Search(" ", OrigTxtDate, s2 + 1) 'Locate third space
s4 = Application.WorksheetFunction.Search(",", OrigTxtDate, s1) 'Locate comma
 
Month = Mid(OrigTxtDate, s1 + 1, (s2 - s1) - 1)
Day = Mid(OrigTxtDate, s2 + 1, s4 - (s2 + 1))
Year = Right(OrigTxtDate, 4)
 
FormattedDate = convertMonthName2Number(Month) & "/" & Day & "/" & Year
 
NewDate = CDate(FormattedDate)
 
cell.Value = NewDate
 
Next
 
End Sub
 
 
Function convertMonthName2Number(monthName As String) As Integer
 
Dim dtestr As String
 
dtestr = monthName & "/1/2000"
 
Dim dte As Date
 
On Error Resume Next
 
dte = CDate(dtestr)
 
If Err.Number <> 0 Then
  convertMonthName2Number = -999
  Exit Function
End If
 
On Error GoTo 0
 
convertMonthName2Number = Month(dte)
 
End Function

 

[Norie]

SCal

You could shorten your code using the VBA InStr function.

I actually think there's a whole load of ways this could be done.

 

[AndrewKent]

Just got round to sorting this one, Norie, your first post worked wonders, thank you!