Recode text to date

[conceal]

Hello,

In Excel I've a column with all kinds of different dates. The format of the dates is 'Month day, year - time' (example: 'March 7, 2005 - 11:35'. The problem is that it's saved as text. I've copied these dates from a table form a website and can't edit it there. Since this will be a common handeling, I would like to have some kind of VBA-code that with recode this text to a date. Is this possible? Thanks allready for your effort!

Greetings,
Maik

 

[macropod]

Hi Maik,

The following macro converts date strings in the selected range to dates that you can format as dates via Format|Cells|Number|Date.

Sub Str2Date()
Dim DtRange As Range
Dim oCell As Range
    If Selection.Cells.Count = 1 Then
        Set DtRange = ActiveCell
    Else
        Set DtRange = Selection
    End If
With Application
    On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange
    For Each oCell In DtRange.SpecialCells(xlConstants)
        oCell.Value = CDate(oCell.Text)
    Next oCell
End With
End Sub

Note: If the cells are formatted as text, you should format them as 'General before running the code.
Cheers

[MS MVP - Word]

 

[conceal]

Hi Macropod,

Sadly I'm not that familiar with VBA that I understand what the macro does. I've tried to run it (even with the selection of the cells), but nothing happens. I've found out that the problem in the program I got from someone else has to do with the software I'm using. I'm using Excel 97 and the commando 'Replace' can not be used.

greetings Maik

 

[macropod]

Hi Maik,

Changing a string to a date won't necessarily change its appearance. The quickest way to tell would be to format the cell as something other than a date (eg 'general') after running the code, or referencing it with a date/value-based formula from another cell.

Cheers
PS: There's no 'replace' call in this macro.

[MS MVP - Word]

 

[conceal]

Hi Macropod,

Sorry for my incomplete information. What I meant was: Someone else gave me this code:

Function myDate(text As String) As Date
    time = Right(text, 5)
    withoutscore = Replace(text, "-", "")    
    myDate = DateValue(withoutscore) + TimeValue(time)
End Function

The code works perfect to convert the 'month day, year - time' format to just the date. But since I'm using Excel 97 I can't use the 'Replace' command.

 

[GlennUK]

For Excel 97:

Function myDate(text As String) As Date
    Time = Right(text, 5)
    withoutscore = Application.Replace(text, Application.Find("-", text) - 1, 99, "")
    myDate = DateValue(withoutscore) + TimeValue(Time)
End Function

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[conceal]

Hi Glenn,

Thanks!!! This was what I've been searching for! Bye the way, I'm still programming that optimisation program to recude the number of calculation, but since I've to do that in my spare time, it goes slowly. I've got a bug somewhere. Nevertheless, I'll post it as soon as possible!!!

Greetings,
Maik

 

[GlennUK]

Hi Maik,
glad that helped you. And good luck on that optimisation program ... I can imagine that there's a lot of work involved.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.