Isolate Times from Custom Date

[Livin404]

Good evening, I am trying to figure out how I can isolate a 24 hour time from a Custom Date format (dd mmm yyyy hhmm) in Excel using VBA. Image A illustrates Column D prior to my VBA. Image B illustrates what happens when I use the VBA. Image C is how I would like it to appear. I'm sure a need a code that will change the format to text in order to keep the leading 0. Also I need to be mindful to have it change only the cells with times. I want to avoid having Value errors in the blank columns. Lastly if it's not too difficult, a code I can use to show there are no errors would be great.
Thank you,

[ATTACH type="full"]24937[/ATTACH]Sub IsolateTime()
    Dim cel As Range

    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    
    For Each cel In Application.Selection.Cells
        cel.Value = Right(cel.Text, 4)
    Next cel

    Selection.NumberFormat = "@"
End Sub

 

[offthelip]

You can extract the time from a cell which contains date and time using this formula:
=D2-INT(D2)
Then format the cell as time however you want it displayed.
So you don't need to use vba. If you really want to use vba you can use the same idea. this just relies on the fact that excel stores dates and times as numbers where the integers are days and the decimal part is the time as a fraction of a day.

 

[Livin404]

You can extract the time from a cell which contains date and time using this formula:
=D2-INT(D2)
Then format the cell as time however you want it displayed.
So you don't need to use vba. If you really want to use vba you can use the same idea. this just relies on the fact that excel stores dates and times as numbers where the integers are days and the decimal part is the time as a fraction of a day.

This is one of 20 VBAs I have for this particular project, and multiple people will be using moreover, I need to have so the cells can be edited. I definitely want to keep this as a VBA. Thank you, for your input.

 

[offthelip]

if you want to do it with vba this code will do it using the same technique:

  Dim cel As Range
   lastrow = Cells(Rows.Count, "D").End(xlUp).Row
   inarr = Range(Cells(2, 4), Cells(lastrow, 4))
    For i = 1 To lastrow - 1
     inarr(i, 1) = inarr(i, 1) - Int(inarr(i, 1))
    Next i
    Range(Cells(2, 4), Cells(lastrow, 4)) = inarr
    Range(Cells(2, 4), Cells(lastrow, 4)).NumberFormat = "h:mm"

Note I load all the data into a variant array because it is faster and I just hate looping through a worksheet

 

[Livin404]

if you want to do it with vba this code will do it using the same technique:

  Dim cel As Range
   lastrow = Cells(Rows.Count, "D").End(xlUp).Row
   inarr = Range(Cells(2, 4), Cells(lastrow, 4))
    For i = 1 To lastrow - 1
     inarr(i, 1) = inarr(i, 1) - Int(inarr(i, 1))
    Next i
    Range(Cells(2, 4), Cells(lastrow, 4)) = inarr
    Range(Cells(2, 4), Cells(lastrow, 4)).NumberFormat = "h:mm"

Note I load all the data into a variant array because it is faster and I just hate looping through a worksheet

Thank you for getting back to me on this. I tried the code you suggested and I ended up with a Run-time error '13' Type Mismatch. I enclosed an image. Once this VBA is working properly I'll move it within the Module. Many thanks.

 

[offthelip]

That implies that the value in column D is not a time valued stored as a number as per the EXCEl format. Use debug to look at the value of I and you can then see if it is just one row or all rows.

 

[Livin404]

That implies that the value in column D is not a time valued stored as a number as per the EXCEl format. Use debug to look at the value of I and you can then see if it is just one row or all rows.

I know it’s in a custom format dd mmm yyyy hhmm.

 

[offthelip]

try this code that check to see if the cell contains text:

Sub test()
  Dim cel As Range
  Dim myrange As Range
   lastrow = Cells(Rows.Count, "D").End(xlUp).Row
   Set myrange = Range("d2:d" & lastrow)
    For Each cel In myrange.Cells
        If Not (Application.IsText(cel)) Then
         cel.Value = cel.Value - Int(cel.Value)
        Else
         MsgBox (cel.Row & " is not an excel date time format")
        End If
     Next
    Range(Cells(2, 4), Cells(lastrow, 4)).NumberFormat = "h:mm"
End Sub