Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Date string to date format | Excel Discussion (Misc queries) | |||
Why does Excel mis-convert a string to a date | Excel Discussion (Misc queries) | |||
Help: How do I convert a text date into a real date format | Excel Worksheet Functions | |||
Convert text string to date | Excel Worksheet Functions | |||
How do I convert a text string into a date? | Excel Worksheet Functions |