VBA convert day and date from text string to Excel date
 

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).

VBA convert day and date from text string to Excel date
 

=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

"Max Bialystock" wrote:
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).

VBA convert day and date from text string to Excel date
 

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

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 Peterson

VBA convert day and date from text string to Excel date
 

Thanks Dave.


"Dave Patrick" wrote in message
...
=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

"Max Bialystock" wrote:
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).

VBA convert day and date from text string to Excel date
 

That's terrific.
Thanks Dave.


"Dave Peterson" wrote in message
...
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 wrote:

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 Peterson

VBA convert day and date from text string to Excel date
 

You're welcome.

--

Regards,

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

"Max Bialystock" wrote:
Thanks Dave.