Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recode text to date

Status
Not open for further replies.

conceal

Technical User
Apr 1, 2007
14
NL
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
 
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.
Code:
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]
 
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
 
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]
 
Hi Macropod,

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

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.
 
For Excel 97:
Code:
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.
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top