Results 1 to 5 of 5

Converting Date:Time text to Date:Time value

Threaded View

  1. #1
    Registered User
    Join Date
    10-10-2009
    Location
    BKK, Thailand
    MS-Off Ver
    Excel 2003
    Posts
    3

    Converting Date:Time text to Date:Time value

    Hello, I'm trying to write a VBA code for converting the date/time as text to a date/time value.

    The scenario is, I have three different types of text :
    ddmmmyy
    HH:MM
    ddmmmyy:HHMM

    I could have the code working fine with the first two types, but not the last one, please help me with this.

    Here is my code

    Private Sub StringToDateTime_Btn_Click()
    Dim c As Range
    Dim timeStr, timeStr2, timeStr3 As String
    
        For Each c In Selection
        On Error Resume Next
        Select Case True
    
        Case c.Text Like "##???##"
            timeStr = Format(c.Value, "ddmmmyy")
            c.NumberFormat = "[$-1010409]ddmmmyy;@"
            c.Value = timeStr
            
        Case c.Text Like "##:##"
            timeStr = TimeValue(c.Value)
            c.NumberFormat = "[$-1000000]hh:mm"
            c.Value = timeStr
       
        Case c.Text Like "##???##:####"
            
            'timeStr = Format(Mid(c.Value, 1, 7), "ddmmmyy")
            'timeStr2 = Mid(c.Value, 9, 2) & ":" & Mid(c.Value, 11, 2)
            'timeStr2 = Format(TimeValue(Format(timeStr2, "HH:MM")), "HHMM")
            'timeStr = TimeValue(timeStr & ":" & timeStr2)
            
            
            
           ' timeStr = Format(c.Value, "ddmmmmyy")
            'MsgBox Format(c.Value, "ddmmmmyy")
           ' c.NumberFormat = "[$-1010409]ddmmmyy:HHMM;@"
            'c.Value = timeStr
        
        End Select
        
    
            
    
        Next c
    End Sub
    Last edited by kacharuk; 10-13-2009 at 05:51 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1