Printable View
First post here, am impressed with Ricks help in mr excel, so decided to join here.
Would like this in vba if possible please
Ok here are my cells in E:E starting E2
2015-10-29T02:45:00+00:00
2015-10-29T03:20:00+00:00
2015-10-29T03:05:00+00:00
2015-10-29T04:52:00+00:00
2015-10-29T04:03:00+00:00
2015-10-29T04:40:00+00:00
2015-10-29T06:00:00+00:00
Need to extract to F2 down, Time formatted as HH:MM EG: 02:45 PM
any help will be appreciated
Graham
Welcome to board!!
Why 02:45 PM ?
The values you have shows are in date format or text format?
Hi
A formula approach and format cells as "hh:mm AM/PM"
=--MID(E2,FIND(":",E2)-2,8)
See if this macro does what you want...Quote:
Originally Posted by Feebles
The above is a general solution only requiring only that an upper case T is in the cell. If the date portion of the cell's value always has a two-digit month number and a two-digit day number, then this slightly simpler code can be used...Code:Sub GetTimeValues()
Dim Cell As Range
For Each Cell In Range("E2", Cells(Rows.Count, "E").End(xlUp))
Cell.Offset(, 1).Value = TimeValue(Split(Split(Cell.Value, "T")(1), "+")(0))
Next
End Sub
Code:Sub GetTimeValues()
Dim Cell As Range
For Each Cell In Range("E2", Cells(Rows.Count, "E").End(xlUp))
Cell.Offset(, 1).Value = TimeValue(Mid(Cell.Value, 12, 8))
Next
End Sub