VBA convert day and date from text string to Excel date

[Max Bialystock]

Range("A1") contains the text string "Friday, 4 May 2007" as imported.

I need a routine that will convert "Friday, 4 May 2007" to 39206 (excel date
number).

 

[Dave Patrick]

=DATEVALUE(RIGHT($A$1,(LEN($A$1)-FIND(",",$A$1,1))))


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[Dave Peterson]

You really want the serial date number?

Option Explicit
Sub testme()

Dim myDate As Date
Dim CommaPos As Long
Dim myCell As Range
Dim myRng As Range

Set myRng = Selection

For Each myCell In myRng.Cells
With myCell
CommaPos = InStr(1, .Value, ",", vbTextCompare)
If CommaPos > 0 Then
myDate = DateSerial(0, 0, 0)
On Error Resume Next
myDate = CDate(Mid(.Value, CommaPos + 1))
On Error GoTo 0

If myDate = DateSerial(0, 0, 0) Then
'skip it, it wasn't a recognizeable date
Else
.NumberFormat = "General"
.Value = CLng(myDate)
End If
End If
End With
Next myCell
End Sub

 

[Max Bialystock]

Thanks Dave.

=DATEVALUE(RIGHT($A$1,(LEN($A$1)-FIND(",",$A$1,1))))


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Range("A1") contains the text string "Friday, 4 May 2007" as imported.

I need a routine that will convert "Friday, 4 May 2007" to 39206 (excel
date number).

 

[Max Bialystock]

That's terrific.
Thanks Dave.

You really want the serial date number?

Option Explicit
Sub testme()

Dim myDate As Date
Dim CommaPos As Long
Dim myCell As Range
Dim myRng As Range

Set myRng = Selection

For Each myCell In myRng.Cells
With myCell
CommaPos = InStr(1, .Value, ",", vbTextCompare)
If CommaPos > 0 Then
myDate = DateSerial(0, 0, 0)
On Error Resume Next
myDate = CDate(Mid(.Value, CommaPos + 1))
On Error GoTo 0

If myDate = DateSerial(0, 0, 0) Then
'skip it, it wasn't a recognizeable date
Else
.NumberFormat = "General"
.Value = CLng(myDate)
End If
End If
End With
Next myCell
End Sub

 

[Dave Patrick]

You're welcome.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect