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: Extract hour ONLY from a date/time field

Discussion in 'Business Applications' started by prog, Feb 26, 2014.

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

    prog Thread Starter

    Joined:
    Feb 26, 2014
    Messages:
    2
    This one has got me. Has anyone figured out a way to do this easily? I can create a new column and extract the right characters and make it work ... but just wondering if there is a quicker way out there!

    Thanks in advance for your time/help ...
     
  2. prog

    prog Thread Starter

    Joined:
    Feb 26, 2014
    Messages:
    2
    Ok, got it to work ... but not sure if there is a better way.

    I created a 2nd colummn and used the =RIGHT(D107,5) to isolate the time (10:32) from the date/time field (JAN 12,2014 10:32)

    I then created another column and used =TIME(HOUR(C107),0,0) to pull out the hour ONLY in 10:00 AM format.

    Is there a quicker way out there?
     
  3. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    =TEXT(D107-int(D107),"h:mm AM/PM")

    This assumes that D107 is recognised by Excel as being a date / time in the first place.

    Excel uses a serial system for dates and times. Dates are whole numbers (why I use the INT function to get the whole number) and times are expressed as a decimal figure as a share of 24 hours. 10:32 AM is about .44 of the day. My formula takes off the whole number from the date and time and then formats it as "10:32 AM".


    A problem you might not have considered is that by discarding the date, you can't use that 10:32 for calculations such as how long from that time to another time.


    A better way is just link to D107 and present it differently ...


    =D107


    and use Time formatting in the cell to show it as you wish


    The time format is: h:mm AM/PM
     
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/1120799