[RESOLVED] taking specific value using string manipulation function

can someone tell me ?how should i take only time part using left,right,Mid
function .any help would be highly appreciated.

Code:

---

7/23/2011 11:40:00 PM
 7/23/2011 11:40:00 PM
 7/23/2011 11:40:00 PM
 7/24/2011 10:41:01 AM
 7/24/2011 12:40:27 PM
 7/24/2011 12:42:34 PM
 7/24/2011 2:08:28 PM
 7/24/2011 2:08:28 PM
 7/24/2011 2:08:28 PM
 7/24/2011 2:08:28 PM
 7/24/2011 2:08:28 PM
 7/24/2011 2:30:58 PM
 7/24/2011 3:59:24 PM
 7/24/2011 5:35:10 PM
 7/24/2011 5:56:08 PM
 7/24/2011 5:56:36 PM
 7/24/2011 5:56:36 PM
 7/24/2011 7:00:55 PM
 7/24/2011 7:00:55 PM
 7/24/2011 7:00:55 PM
 7/24/2011 7:00:55 PM
 7/24/2011 8:33:40 PM
 7/24/2011 8:33:40 PM
 7/24/2011 9:30:25 PM
 7/24/2011 9:31:42 PM
 7/24/2011 9:50:54 PM

---

I guess you mean something like this:

DateAndTime = "7/23/2011 11:40:00 PM"


TimePart = Mid(DateAndTime, 11)

however, if month will have 2-digits then

TimePart = Mid(DateAndTime, 12)

There could be several approaches, although most of them would use Mid$:

Code:

---

Option Explicit

Private Sub Command1_Click()
Dim myDateTime As String
Dim myParts() As String
Dim myTime As String

    myDateTime = "7/24/2011 9:50:54 PM"
   
    'method 1
    myTime = VBA.Mid$(myDateTime, VBA.InStr(1, myDateTime, Space(1)) + 1)
    Debug.Print myTime
   
    'method 2
    myTime = VBA.Mid$(Format(myDateTime, "mm/dd/yyyy h:nn:ss AM/PM"), 12)
    Debug.Print myTime
   
    'method 3
    myParts = VBA.Split(myDateTime, VBA.Space$(1))
    myTime = myParts(1) & Space(1) & myParts(2)
    Debug.Print myTime
   
    'etc...

End Sub

---

If you can think outside the box, the format function will work well for you.

Code:

---

Private Sub Command1_Click()
Dim strDateTime As String

    strDateTime = "7/23/2011 11:40:00 PM"

    MsgBox GetTime(strDateTime)
   
End Sub


Private Function GetTime(ByVal DateTime As String) As String
Dim dteInput As Date

    dteInput = CDate(DateTime)
    GetTime = Format(dteInput, "HH:NN:SS AM/PM")
End Function

---