1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - Extracting Hours and Minutes from a Date

Discussion in 'Business Applications' started by Gogs, Mar 14, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Gogs

    Gogs Thread Starter

    Joined:
    Jul 28, 2003
    Messages:
    121
    Hi,

    I have a column that contains a date and time in the dd/mm/yy hh:mm format
    what I want to do is extract the hh:mm bit into the column next to it so I can then sort by the time. I have tried putting in C2: =B2 for example and formatting it as hh:mm which displays what I want but when I sort it it seems to remember the "hidden" dd/mm/yy bit so i have it in time order but for each day, which is not what i want.

    I have also tried in C2: =RIGHT(B2,5) but that converts it into a number, regardless of what format I try.

    Any ideas?

    Gogs
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Copy the entire column to a blank "helper" column. Next select the newly copied column and then click DATA --> TEXT TO COLUMNS and manually add a delimeter between the date and time to break into seperate cells.

    Regards,
    Rollin
     
  3. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    = TEXT(A1,"hh:mm") will separate hours and minutes out. But you wouldn't be able to sort by it

    you could do a =HOUR(B11)+MINUTE(B11)/60 to get a number and that SHOULD sort although I just tried it and got strange results. Don't have time to mess now but maybe there is something there you can use.

    - Castleheart :cool:
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Use the TIME() function, sort on that column.
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    My personal fave for this is INT, in that it underlines the concept that dates in Excel are just plain ol' numbers. Hence:

    =value-INT(value)
     
  6. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    Hey Zack and Andy,

    I like MOD to separate time from date/time cell.

    =MOD(A1,1)

    and format the cell that houses the formula as Time...

    Long time no see to both of you. Hope all is well with you and your families...:D
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    Even better ; nice one, Brian. (y)
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Hiya Brian! Long time no see. How's my favorite island doing? Holding down the fort? ;)

    Take care :)
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/693215