+ Reply to Thread
Results 1 to 4 of 4

Converting MM/DD/YY HH:MM:SS to HH:00

  1. #1
    Registered User
    Join Date
    09-21-2011
    Location
    Lansdale PA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Converting MM/DD/YY HH:MM:SS to HH:00

    Everyone,

    My first time here so I hope I detail my issue correctly.

    I have a spreadsheet which has a date column and the dates are in the format MM/DD/YY HH:MM:SS.

    I need to group the corresponding values in the the other columns based on 'hour of day' . In other words...

    9/21/2011 14:11:45
    9/21/2011 14:32:11
    9/21/2011 17:01:03

    should read as

    9/21/2011 14:00
    9/21/2011 14:00
    9/21/2011 17:00



    Is this possible?
    Last edited by vikidisi; 09-21-2011 at 07:18 PM. Reason: SOLVED

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Converting MM/DD/YY HH:MM:SS to HH:00

    Hi vikidisi

    Welcome to the Forum....

    One way.

    Assuming your dates are in A2 Dowm
    In B2
    Please Login or Register  to view this content.
    Drag/Fill Down.

    Format the column
    Number > Custom Type:=
    Please Login or Register  to view this content.
    Hope this helps
    Last edited by Marcol; 09-21-2011 at 07:11 PM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Registered User
    Join Date
    09-21-2011
    Location
    Lansdale PA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Converting MM/DD/YY HH:MM:SS to HH:00

    You are awesome! - Thanks

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Converting MM/DD/YY HH:MM:SS to HH:00

    As you are effectively rounding down to the previous whole hour you can also use FLOOR function here, i.e.

    =FLOOR(A2,"1:00")

    format as required
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1