Results 1 to 5 of 5

Thread: Extract Time from a Date&time cell

  1. #1
    Junior Member
    Join Date
    Oct 2015
    Posts
    1
    Rep Power
    0

    Extract Time from a Date&time cell

    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

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Welcome to board!!

    Why 02:45 PM ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    13
    The values you have shows are in date format or text format?

  4. #4
    Junior Member
    Join Date
    Dec 2012
    Posts
    16
    Rep Power
    0
    Hi

    A formula approach and format cells as "hh:mm AM/PM"

    =--MID(E2,FIND(":",E2)-2,8)

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Feebles View Post
    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
    See if this macro does what you want...
    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
    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(Mid(Cell.Value, 12, 8))
      Next
    End Sub
    Last edited by Rick Rothstein; 11-09-2015 at 06:35 AM.

Similar Threads

  1. Replies: 0
    Last Post: 10-23-2013, 04:58 AM
  2. Calculate Time Difference Between Time In HH:MM
    By Stalker in forum Excel Help
    Replies: 8
    Last Post: 03-28-2013, 03:27 PM
  3. Date & Time Formats in Different Languages
    By Haseeb A in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 06-10-2012, 02:57 PM
  4. Displayin Date/Time in "original" time zone
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-21-2012, 02:02 AM
  5. Spreading a time range (shift time, etc) in columns.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 1
    Last Post: 08-23-2011, 11:45 AM

Posting Permissions

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